1093 Error in MySQL table is specified twice

Question

I'm trying to update some rows. I only want to update the rows that have ID=0.

The error message I'm receiving is:

1093 - Table 'ch_15_posts' is specified twice, both as a target for 'UPDATE' and as a separate source for data

This is the code I'm using. Any suggestions would be welcome!

UPDATE ch_15_posts SET ID = (select MAX(ID)+1 as max FROM `ch_15_posts`)
WHERE ID = 0

Answer 1

MySQL doesn't allow you to SELECT from a table in the same statement where you UPDATE or DELETE that same table.

mysql> UPDATE ch_15_posts SET ID = (select MAX(ID)+1 as max FROM `ch_15_posts`) where id = 0;
ERROR 1093 (HY000): You can't specify target table 'ch_15_posts' for update in FROM clause

There's a workaround to do a sort of double subquery which evaluates the inner subquery earlier, and storing the result in a temporary table. However, this won't get you what you want, because it only runs the sub-sub-query once, and it will generate a single value and assign it to all your rows where id = 0.

mysql> UPDATE ch_15_posts SET ID = (select max from (select MAX(ID)+1 as max FROM `ch_15_posts`) t) where id = 0;
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3  Changed: 3  Warnings: 0

It looks like you're trying to assign auto-incrementing values to rows where you inadvertently set the value 0. You can't use the MAX(id)+1 method without locking the table, because other concurrent sessions might be inserting new rows while you're doing it. So it's a race condition.

But you can backfill auto-increment values atomically by making the column an auto-increment key.

Demo:

mysql> create table c_15_posts (id int );

mysql> insert into c_15_posts values (0), (2), (0), (6), (0), (42);
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> alter table c_15_posts modify id int auto_increment primary key;
Query OK, 6 rows affected (0.04 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from c_15_posts;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  6 |
|  7 |
| 42 |
+----+

The rows with 0 don't start at 43, but they do receive unique values. The next insert will get id 43.

Answer 2

You probably need something like this:

UPDATE ch_15_posts AS t1
JOIN (select MAX(ID)+1 as max FROM `ch_15_posts`) AS t2
SET t1.ID = t2.max
WHERE ID = 0