Wednesday, October 14, 2009

MySQL: Updating from the same table

You might ask 'why' I am doing this, but that's not the point. To update a field in a table in mysql, from the same table (which MySQL disallows):

UPDATE mytable
set myvalue = myvalue+(select myvalue FROM (select myvalue from mytable where year='2008') b )
where
year = 2009


Basically, you have to trick mysql into selecting the subquery from a temporary table; as done above.

Kudos to Peter Geer for the idea for this.

No comments:

Post a Comment