delete all record from table in mysql

Question

I am trying to delete all records from a table. My query is:

delete from tableName.

But it reports the following error:

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor -> Query Editor and reconnect."

How do I resolve this?

I might be missing something, but the error message already tells you what to do: *toggle the option in Preferences -> SQL Editor -> Query Editor and reconnect.* — , Nov 11 '11 at 07:53
Ok...MySQL Workbench is telling how to solve this: *toggle the option in Preferences -> SQL Editor -> Query Editor and reconnect.*. Have you tried that? If you have and it still doesn't work then flag to re-open. — Kev, Nov 11 '11 at 11:05
There are two solutions here 1. delete from table where 1=1 . 2. truncate — lokeshjain2008, Jan 23 '14 at 14:42
I had this problem today and resolved it by adding a limit clause `delete from tableName limit 1000`, you can replace 1000 by whatever the count of the table is (`select count(*) from tableName`) — Bug Killer, Sep 18 '14 at 14:49
I learned there was a setting for when deleting in mysql from this not real question — Lealo, Aug 17 '17 at 03:12

Thirler · Answer 1 · 2017-02-06T08:27:45.427

94

truncate tableName

That is what you are looking for.

Truncate will delete all records in the table, emptying it.

edited Feb 06 '17 at 08:27

answered Nov 11 '11 at 07:47

Thirler

20,239
14
63
92

1

This command drops the table. It doesn't delete all the records from the table. – pg2455 Feb 03 '17 at 22:27
1

@mousecoder This is not correct, it will leave an empty table, where drop removes the table. Added the link to the manual. – Thirler Feb 06 '17 at 08:28
Agree.. pardon my incorrect knowledge. – pg2455 Feb 06 '17 at 22:36
3

this wont work if there is FK and cascading enabled – naamadheya Apr 10 '17 at 06:51
yeah... This failed for me for the aforementioned reason – the_mackster Nov 28 '22 at 15:19

score 69 · Answer 2 · edited Aug 01 '18 at 11:58

69

It’s because you tried to update a table without a WHERE that uses a KEY column.

The quick fix is to add SET SQL_SAFE_UPDATES=0; before your query :

SET SQL_SAFE_UPDATES=0;

Or

close the safe update mode. Edit -> Preferences -> SQL Editor -> SQL Editor remove Forbid UPDATE and DELETE statements without a WHERE clause (safe updates) .

BTW you can use TRUNCATE TABLE tablename; to delete all the records .

edited Aug 01 '18 at 11:58

jincy mariam

589
6
7

answered Nov 11 '11 at 07:59

Sandeep Pathak

10,567
8
45
57

For the record, the latest version (and may be the previous ones) also fail when you use TRUNCATE TABLE. Also, the error is still the same, however the option is in Edit->Preferencie -> SQL Queries (and not in SQL Editor). – magallanes Oct 19 '12 at 14:53

delete all record from table in mysql

2 Answers2

Linked