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屋!