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    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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] mysql delete under safe mode

Examples related to mysql

Examples related to sql