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:
Asked by roland luo
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 );
Answered by rutter
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
In Mysql Workbench 184.108.40.206, you can disable safe mode.
Edit menu => Preferences => SQL Editor : Other section: click on “Safe updates” … to uncheck option
Answered by Peter B
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