How to use table aliases with MYSQL UPDATE

Question

I have to update a mysql table using PHP's mysql_query() function. Therefore I need to send the following UPDATE query in only one statement:

SET @i = 0;

UPDATE mytable SET id=(@i:=@i+1);

I have read some examples where this can be done with SELECT statements using table aliases like:

SELECT @rn:=@rn+1 AS rank, t1.* FROM (SELECT * FROM mytable) t1, (SELECT @rn:=0) t2

Is there some way to use these table aliases with the UPDATE statement that I need to use?

Edit:

Based on Topher Hunt's answer, I think I could create a copy of mytable using:

CREATE TABLE mytable_copy SELECT @rn:=@rn+1 AS id, t1.* FROM (SELECT * FROM mytable) t1, (SELECT @rn:=0) t2

Then DROP mytable and RENAME mytable_copy to mytable.

Would this statement create an exact copy of mytable, with the same field types and lenghts that the ones in mytable?

Answer 1

If you use JOINs between your tables, the syntax is very similar between SELECT and UPDATE statements. Example:

SELECT * 
FROM table1 a
    JOIN table2 b ON a.something = b.something ## (conditions for linking tables)
    JOIN table3 c ON b.something = c.something
WHERE a.something = 'value'

A SELECT statement that joins 3 tables, can be turned around into an UPDATE statement just by removing the SELECT line, changing the word FROM to UPDATE, and adding a "SET" clause to say what you want to change. Like this:

UPDATE table1 a
    JOIN table2 b ON a.something = b.something ## (conditions for linking tables)
    JOIN table3 c ON b.something = c.something
SET a.variable = 'value2', 
    b.something = 'value3',
    c.somethingelse = 'value4'
WHERE a.something = 'value';

You can make changes to lots of fields at the same time, if needed; just separate each SET item with commas.