mysql在安全模式下备份,在安全模式下删除mysql

I have a table instructor and I want to delete the records that have salary in a range

An intuitive way is like this:

delete from instructor where salary between 13000 and 15000;

However, under safe mode, I cannot delete a record without providing a primary key(ID).

So I write the following sql:

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

However, there is an error:

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

I am confused because when I write

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

it does not produce an error.

My question is:

what does this error message really mean and why my code is wrong?

how to rewrite this code to make it work under safe mode?

Thanks!

解决方案

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

);