Archive for August 2009
I got stuck moving back to MySQL 4.1 from 5, where I had an UPDATE statement that operated on a subselect from the same table. In 4.1 I got the error “You can’t specify target table ‘x’ for update in FROM clause” due to the self-referring subselect. This syntax allowed me to work around the problem:
UPDATE attributes AS t1,
(SELECT MAX(ordering)+1 AS ordering FROM attributes WHERE parentid='1') AS tt
SET t1.ordering = tt.ordering
WHERE t1.parentid='1' AND t1.ordering='-1'
Here the subselect is stored in a different recordset so MySQL doesn’t mind the self-reference.