Mysql SELECT inside UPDATE

Question

UPDATE forms SET

pos = (SELECT MIN(pos)-1 FROM forms)

WHERE id=$id

This doesn't work, error message:

**You can't specify target table 'form' for update in FROM clause**

I hope it's clear: I want to get the minimal element-1 from the same table and assign it to pos

Answer 1

Consp is right that it's not supported. There's a workaround, however:

UPDATE forms SET
pos = (SELECT MIN(pos)-1 FROM (SELECT * FROM forms) AS x)
WHERE id=$id

A version that is probably faster:

UPDATE forms 
SET pos = (SELECT pos-1 FROM (SELECT MIN(pos) AS pos FROM forms) AS x)
where id=$id