7
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

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Dan
  • 55,715
  • 40
  • 116
  • 154

4 Answers4

12

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
Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • +1 for "screw your arbitrary whimsical restrictions". hope it works though, i don't use mysql myself anymore. – just somebody Dec 24 '09 at 00:07
  • ... guys do not forget to use isnull() in case the table is empty – Dan Dec 24 '09 at 00:32
  • I have tried to make it a little faster, but I haven't measured the performance. You may have to play around a bit to see what works best for your data. – Mark Byers Dec 24 '09 at 00:38
  • 4
    It's not an arbitrary whimsical restriction... It's because the SELECT should result in locking the relevant rows, but the rows are already being locked by the UPDATE statement, and no one's coded the logic to do proper locking without causing the statement to potentially deadlock itself. -- Note that by creating a temporary table that you're selecting into, this UPDATE statement isn't actually atomic, and multiple UPDATEs with the same pos could potentially be made. – Conspicuous Compiler Dec 24 '09 at 04:58
  • Such request created an endless process and my server became unavailable. This may be due to the additional functionality that I included in the request, but be careful. The [transactions](https://stackoverflow.com/a/1956014/6263942) worked for me without any problems. – Gleb Kemarsky Oct 23 '20 at 06:52
5

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.

Conspicuous Compiler
  • 6,403
  • 1
  • 40
  • 52
1

You could also try:

START TRANSACTION;
SET @newMin := MIN(pos)-1 FROM forms;
UPDATE forms SET pos=@newMin WHERE id='$id';
COMMIT;
cmptrgeekken
  • 8,052
  • 3
  • 29
  • 35
  • For MariaDB second line should start with `SELECT` instead of `SET`: https://mariadb.com/kb/en/start-transaction/#examples – Gleb Kemarsky Oct 23 '20 at 06:57
1

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 ”

RAO
  • 11
  • 3