mysql delete under safe mode

Question

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:

1. what does this error message really mean and why my code is wrong?
2. how to rewrite this code to make it work under safe mode?

Thanks!

Answer 1

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.