Tuesday, April 19, 2011

How to update a mysql database record with value from another record in the same table

I have stumbled upon a problem that is not so uncommon. I wanted to set parent id of child record. I could always do something like:

update news_category set parent = 100;

But that was not the option because there was no way I can be 100% sure that 100 was id of a correct parent on any other database except mine.

When we try to do something like:

update news_category set parent = (select id from news_category where name = 'parent');
we get an error. This is the point where people give up and write a script in php or something, not knowing that this problem can solved by simple aliasing.
If we use a simple trick an convince the mysql that we are using two tables, one for update and one for select, it will work.

Update looks like:

update news_category set parent = (select id from (select id from  news_category where name = 'parent') ct) where name = 'child';
Update statement for mysql is exactly the same as
update news_category set parent= (select id from ct) where name = 'child';
because we used table alias.

Post a Comment