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.

Resetting MySQL Root Password

Resetting the root password for MySQL can be done by using the following commands:


1: use mysql;
2: UPDATE user SET Password = PASSWORD("newpassword") WHERE user = 'root';
3: flush privileges;


(Tested on MySQL 5.1.11)