在安全模式下删除mysql

Question

我有一位表格讲师，我想删除工资范围在一定范围内的记录。一种直观的方式是这样的：

delete from instructor where salary between 13000 and 15000;

但是，在安全模式下，如果不提供主键（ID），则无法删除记录。

所以我写下面的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);

它不会产生错误。

我的问题是：

1. 此错误消息的真正含义是什么，为什么我的代码是错误的？
2. 如何重写此代码以使其在安全模式下工作？

谢谢！

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 ...);

例外：如果对FROM子句中的已修改表使用子查询，则上述禁止条件不适用。例子：

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演示。