Mysql SELECT inside UPDATE

mysql select sql-update mysql-error-1093
Dan picture Dan · Dec 24, 2009 · Viewed 14.6k times · Source 
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

Mark Byers picture Mark Byers · Dec 24, 2009

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

Related questions

MySQL - UPDATE query based on SELECT Query

I need to check (from the same table) if there is an association between two events based on date-time. One set of data will contain the ending date-time of certain events and the other set of data will contain the …

Read More
MySQL - Update values based on subquery

let's say I have select, which return me from table1: ID Name 1 Bob 2 Alice 3 Joe Then I want UPDATE values in another table based on this result: UPDATE table2 SET Name = table1.Name WHERE ID = table1.ID As I understood, …

Read More
How to add a column to a table from another table in Mysql?

I have two tables table1 table2 Tabel1 contains 2 columns id Name Tabel2 contains 2 columns id Age A want to add age column from table2 to table1 (WHERE table1.id = table2.id) Then table1 should contains 3 columns id Name Age

Read More