Mysql delete in safe mode

Question

I have a table instructor and I want to delete salary entries in a range Intuitive way:

delete from instructor where salary between 13000 and 15000; 

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

So, I am writing 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'm confused because when I write

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

it does not cause an error.

My question is:

• What does this error message really mean and why is my code incorrect?
• How to rewrite this code so that it works in safe mode?

Thank!

Answer 1

It seems like a popular answer looks like "just disable safe mode" :

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

To be honest, I can’t say that I’m ever used to working in safe mode. However, I do not quite agree with this answer, as it simply assumes that you should change the database configuration every time you encounter a problem.

So, your second query is closer to the sign, but has a different problem: MySQL applies several restrictions for subqueries, and one of them is that you cannot change the table when you select from it in the subquery.

Quote from the MySQL manual, Subquery restrictions :

In general, you cannot modify the table and select from the same table in the subquery. For example, this restriction applies to claims in 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 previous ban does not apply if you use a subquery for a modified table in the FROM clause. Example:

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

Here, the result of the subquery in the FROM clause is stored as a temporary table, so the corresponding rows in t were already selected by the time of updating to t.

The last bit is your answer. Select the target identifiers in the temporary table, then delete by specifying the identifiers in this 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 .

Answer 2

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

Assuming you have a table with an automatically incrementing numeric primary key, you can do the following:

 DELETE FROM tbl WHERE id <> 0 

Answer 3

Disabling Safe Mode in Workbench Mysql 6.3.4.0

Edit menu => "Settings" => "SQL Editor": another section: click on "Safe Updates" ... to clear the check box

enter image description here

Answer 4

It appears that your MySql session has a secure update option. This means that you cannot update or delete records without specifying a key (such as a primary key) in the where clause.

Try

SET SQL_SAFE_UPDATES = 0;

Answer 5

I have a much simpler solution; it works for me; It is also a workaround, but can be used and you do not need to change the settings. I assume that you can use a value that will never be there, then you use it in your WHERE clause

DELETE FROM MyTable WHERE MyField IS_NOT_EQUAL AnyValueNoItemOnMyFieldWillEverHave

I don’t like this solution too much, so I’m here, but it works, and it seems better than what it was answered