113

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!

Improve this question
3
  • did you want to keep safe mode on? and are you using mySql workbench? Commented Feb 17, 2014 at 23:48
  • the answer to both your questions are yes. And I am surprised that when I used jdbc to delete records in mysql databases without a PK, it does not produce an error. So the safe mode is only for mysql workbench? Commented Feb 17, 2014 at 23:56
  • 2
    no - I was asking because if you wanted to turn it off in mySQL workbench, I could've told you how. Personally I work with it off... having to have ID's is great safety wise - but development wise, I found it to be a pain Commented Feb 18, 2014 at 0:12

5 Answers 5

Reset to default
281

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Thank you for your explain! However, I tried your code in mysql workbench and it still said "You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column".
That's unexpected. Is your primary key under a different name? I notice your question seems to indicate it's named ID, while my answer uses id.
Yes, I change it to ID and it does not work. I tried delete from instructor where ID = '1' and it works so the ID is the primary key
@rolandluo I know it's been a long time, but I'm curious if you ever figured out a workaround. Clearly this method has worked in other circumstances, so I wonder why your case is different. Something specific to your server or version, perhaps?
That method is not explicitly creating a temp table. You created result set with a label called "temp". If your result set is large enough; mysql may have created a temp table for you (see the explain plain to be sure.) but its not guaranteed. 'CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (SELECT * FROM table1)' definitely creates a temp table which can be used in subsequent queries until the connection is closed.
|
30

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
Improve this answer

1 Comment

This is Best. Using ID alongside with actual query worked. "delete from instructor where salary between 13000 and 15000 AND id <> 0"
16

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

Improve this answer

2 Comments

"how to rewrite this code to make it work under safe mode?" ==> Your answer tells how to disable safe mode ;)
Sorry, I totally misunderstood the question. I found this topic when I was not able to delete from tables with Mysql workbench, and there was a similar question in a comment with mysql workbench, but I can not create comments. I thought, that it would be a good place to write this here for future reference...
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

Improve this answer

Comments

0 
Why the Error Happens Safe update mode requires: 1. A WHERE clause that uses a key column (like a PRIMARY KEY or UNIQUE index). 2. Or, you use a LIMIT clause. If you try to run something like: DELETE FROM instructor WHERE salary BETWEEN 13000 AND 15000; …it fails in safe mode because salary is not a key column. Solutions That Work Without Disabling Safe Mode Option 1: Use Primary Key in the WHERE Clause DELETE FROM instructor WHERE salary BETWEEN 13000 AND 15000 AND id <> 0; -- assuming 'id' is the primary key This satisfies safe mode because it uses the id key column. Option 2: Use a Subquery with a Temporary Result Set DELETE FROM instructor WHERE id IN ( SELECT temp.id FROM ( SELECT id FROM instructor WHERE salary BETWEEN 13000 AND 15000 ) AS temp ); This method avoids modifying and selecting from the same table at once by wrapping the subquery in an alias. Option 3: Create a Temporary Table (Explicit) CREATE TEMPORARY TABLE temp_ids AS SELECT id FROM instructor WHERE salary BETWEEN 13000 AND 15000; DELETE FROM instructor WHERE id IN (SELECT id FROM temp_ids); Then the deletion is based on primary keys from a clearly defined temporary table. Avoid (If You Want to Keep Safe Mode On) SET SQL_SAFE_UPDATES = 0; -- risky, disables safe mode temporarily
Improve this answer

2 Comments

please format your answer. not everything you write has to be formatted as code, only the code itself.
Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.