107

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!

roland luo
  • 1,561
  • 4
  • 19
  • 24
  • did you want to keep safe mode on? and are you using mySql workbench? – wribit Feb 17 '14 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? – roland luo Feb 17 '14 at 23:56
  • 1
    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 – wribit Feb 18 '14 at 00:12

4 Answers4

272

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.

Community
  • 1
  • 1
rutter
  • 11,242
  • 1
  • 30
  • 46
  • 6
    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". – roland luo Feb 18 '14 at 01:07
  • 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`. – rutter Feb 18 '14 at 01:14
  • 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 – roland luo Feb 18 '14 at 01:22
  • 1
    @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? – rutter Apr 17 '15 at 17:11
  • 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. – txyoji Feb 07 '22 at 20:01
27

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
  • 1
    This is Best. Using ID alongside with actual query worked. "delete from instructor where salary between 13000 and 15000 AND id <> 0" – Rajitha Fernando Aug 04 '21 at 05:52
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

crusy
  • 1,424
  • 2
  • 25
  • 54
Peter B
  • 257
  • 2
  • 3
  • 3
    "how to rewrite this code to make it work **under safe mode**?" ==> Your answer tells how to disable safe mode ;) – ByteHamster Apr 02 '16 at 17:36
  • 2
    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... – Peter B Apr 10 '16 at 14:05
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

Joaq
  • 1
  • 1