delete mysql in safe mode

Problem

I’m a table instructor, and I’d like to erase any records with a wage range. This is an example of an intuitive method:

delete from instructor where salary between 13000 and 15000;

In safe mode, however, I am unable to delete a record without first giving a main key (ID).

As a result, I create the following sql:

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

There is, however, a mistake:

You can't specify target table 'instructor' for update in FROM clause

I’m perplexed because when I write, I’m not sure what I’m

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

It does not result in a mistake.

My question is:

Thanks!

Asked by roland luo

Solution #1

After doing some research, it appears that the most common response is to “simply switch 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 being honest, I’ve never made it a practice to run in safe mode. Still, I’m not convinced by this response because it presupposes you to update your database configuration every time you have a problem.

So, while your second query is closer to the mark, it runs into a snag: Subqueries have a few limitations, one of which is that you can’t edit a table while choosing from it in a subquery.

Restrictions on Subqueries, according to the MySQL manual:

That last part is your response. Select target IDs in a temporary table, then delete them using the table’s IDs:

DELETE FROM instructor WHERE id IN (
  SELECT temp.id FROM (
    SELECT id FROM instructor WHERE salary BETWEEN 13000 AND 15000
  ) AS temp
);

SQLFiddle demo.

Answered by rutter

Solution #2

You can deceive MySQL into believing you’re defining a primary key column. You can use this 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

Answered by Hugo Zink

Solution #3

In Mysql Workbench 6.3.4.0, you can disable safe mode.

Edit menu => Preferences => SQL Editor : Other section: click on “Safe updates” … to uncheck option

Answered by Peter B

Solution #4

I have a considerably more basic approach that works for me; it is also a workaround, but it may be usable, and it does not require you to change your settings. I’m assuming you may utilize a value that will never exist in your WHERE clause.

DELETE FROM MyTable WHERE MyField IS_NOT_EQUAL AnyValueNoItemOnMyFieldWillEverHave

I don’t like that approach either, which is why I’m here, but it works and appears to be better than the previous answer.

Answered by Joaq

Post is based on https://stackoverflow.com/questions/21841353/mysql-delete-under-safe-mode