1093 MySQL表中的错误指定了两次 [英] 1093 Error in MySQL table is specified twice

问题描述

我正在尝试更新一些行.我只想更新ID = 0的行.

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

我收到的错误消息是:

这是我正在使用的代码.任何建议都将受到欢迎！

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

推荐答案

MySQL不允许您在同一语句中的表中进行SELECT，而在该语句中您要更新或删除该表.

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

您似乎试图将自动递增值分配给无意间将值设置为0的行.您不能在不锁定表的情况下使用MAX(id)+1方法，因为其他并发会话可能是在执行操作时插入新行.所以这是比赛条件.

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.

演示:

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 |
+----+

具有0的行不是从43开始，但是它们确实接收到唯一的值.下一次插入将获得ID 43.

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

这篇关于1093 MySQL表中的错误指定了两次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！