CODEWITHSUNDEEP
mysqlselectsql-updatemysql-error-1093
Dan
Dan

Reputation: 57971

Mysql SELECT inside UPDATE

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

Upvotes: 7

Views: 15102

Answers (4)

RAO
RAO

Reputation: 11

I think that you can not use a subquery inside an update statement, but any way there are workarounds for it ...

Here is a Quotation from the following site:

"dev.mysql.com"

“Currently, you cannot delete from a table and select from the same table in a sub-query ”

Upvotes: 1

Mark Byers
Mark Byers

Reputation: 839174

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

Upvotes: 12

cmptrgeekken
cmptrgeekken

Reputation: 8092

You could also try:

START TRANSACTION;
SET @newMin := MIN(pos)-1 FROM forms;
UPDATE forms SET pos=@newMin WHERE id='$id';
COMMIT;

Upvotes: 1

Conspicuous Compiler
Conspicuous Compiler

Reputation: 6469

Your problem is stated plainly in the MySQL manual:

Currently, you cannot update a table and select from the same table in a subquery.

You'll want to use a transaction. Turn AutoCommit off, begin a transaction, then do a SELECT MIN(pos)-1 FROM forms FOR UPDATE, take that result, do the update with it, then commit your transaction.

Upvotes: 5

Related Questions