安全模式下删除mysql

Question

delete from instructor where salary between 13000 and 15000;

所以我编写以下SQL：

delete from instructor where ID in (select ID from instructor where salary between 13000 and 15000);

但是，有一个错误：

You can't specify target table 'instructor' for update in FROM clause

我很困惑因为当我写的时候

select * from instructor where ID in (select ID from instructor where salary between 13000 and 15000);

null

Answer 1

谷歌一下，流行的答案似乎是“关闭安全模式就行了”：

SET SQL_SAFE_UPDATES = 0;
DELETE FROM instructor WHERE salary BETWEEN 13000 AND 15000;
SET SQL_SAFE_UPDATES = 1;

如果我是诚实的，我不能说我曾经养成过在安全模式下运行的习惯。不过，我对这个答案并不完全满意，因为它只是假设每次遇到问题时都应该更改数据库配置。

因此，您的第二个查询更接近目标，但遇到了另一个问题：MySQL对子查询应用了一些限制，其中之一是在子查询中选择表时不能修改表。

引用MySQL手册中对子查询的限制：

通常情况下，您不能在子查询中修改表并从同一表中进行选择。例如，此限制适用于以下形式的语句：

DELETE FROM t WHERE ... (SELECT ... FROM t ...);
UPDATE t ... WHERE col = (SELECT ... FROM t ...);
{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);

UPDATE t ... WHERE col = (SELECT * FROM (SELECT ... FROM t...) AS _t ...);

这里，from子句中的子查询的结果存储为临时表，因此在对t进行更新时，t中的相关行已经被选择。

最后一点就是你的答案。在临时表中选择目标ID，然后通过引用该表中的ID进行删除：

DELETE FROM instructor WHERE id IN (
  SELECT temp.id FROM (
    SELECT id FROM instructor WHERE salary BETWEEN 13000 AND 15000
  ) AS temp
);

SQLFiddle演示。