在安全模式下删除

我有一个表导师，我想删除的记录，有一个范围内的薪水 一种直观的方法是这样的:

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. 如何重写此代码，使其在安全模式下工作？

谢谢！

303645 次浏览
小开
最佳答案

Googling around, the popular answer seems to be "just turn off safe mode":

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

If I'm honest, I can't say I've ever made a habit of running in safe mode. Still, I'm not entirely comfortable with this answer since it just assumes you should go change your database config every time you run into a problem.

So, your second query is closer to the mark, but hits another problem: MySQL applies a few restrictions to subqueries, and one of them is that you can't modify a table while selecting from it in a subquery.

Quoting from the MySQL manual, Restrictions on Subqueries:

In general, you cannot modify a table and select from the same table in a subquery. For example, this limitation applies to statements of the following forms:

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

Exception: The preceding prohibition does not apply if you are using a subquery for the modified table in the FROM clause. Example:

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

Here the result from the subquery in the FROM clause is stored as a temporary table, so the relevant rows in t have already been selected by the time the update to t takes place.

That last bit is your answer. Select target IDs in a temporary table, then delete by referencing the IDs in that table:

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

SQLFiddle demo.

小开

Turning off safe mode in Mysql workbench 6.3.4.0

Edit menu => Preferences => SQL Editor : Other section: click on "Safe updates" ... to uncheck option

Workbench Preferences

小开

You can trick MySQL into thinking you are actually specifying a primary key column. This allows you to "override" safe mode.

Assuming you have a table with an auto-incrementing numeric primary key, you could do the following:

DELETE FROM tbl WHERE id <> 0
小开

I have a far more simple solution, it is working for me; it is also a workaround but might be usable and you dont have to change your settings. I assume you can use value that will never be there, then you use it on your WHERE clause

DELETE FROM MyTable WHERE MyField IS_NOT_EQUAL AnyValueNoItemOnMyFieldWillEverHave

I don't like that solution either too much, that's why I am here, but it works and it seems better than what it has been answered


提交答案