SQL WHERE Clause for any value?-CodePudding

I'm executing SQL against a MySQL database and trying to write commands to delete all rows in a table. When I run "DELETE FROM MyTable" I get the error 1175 because a safety feature is turned on by default that forces you to specify a WHERE clause on the primary key.

I could turn this feature off, but I kind of like it, so I'm trying to modify my command to have the same effect by adding the WHERE clause. So, this works:

DELETE FROM MyTable WHERE MyID > 0

In deleting all rows greater where MyID is greater than zero, but I'm looking for a truly universal one that would work for any MyID values. I tried these

DELETE FROM MyTable WHERE MyID = ANY

DELETE FROM MyTable WHERE MyID = *

But they are syntax errors. The doc on the WHERE clause says it just accepts =,<,>,>=,<=,<>,BETWEEN,LIKE,and IN, but nothing that looks like it accepts anything.

Is there a proper syntax for the WHERE conditional that accepts any value?

thank you.

CodePudding user response：

Use

 TRUNCATE MyTable;

then you don't need any condition, as you should have DELETE privileges

TRUNCATE TABLE empties a table. It requires the DROP privilege. Logically, TRUNCATE TABLE is similar to a DELETE statement that deletes all the rows, or a sequence of DROP TABLE and CREATE TABLE statements.

see manual

CodePudding user response：

The error is related to the sql_safe_updates client option (see https://dev.mysql.com/doc/refman/8.0/en/mysql-tips.html#safe-updates).

One way to circumvent the safe updates mode is to use a LIMIT clause on your query. You can use any value, even a value that is far larger than the number of rows in your table.

DELETE FROM MyTable LIMIT 9223372036854775807;

You can also disable the safe updates mode, then run the DELETE normally:

SET sql_safe_updates=0;
DELETE FROM MyTable; -- returns no error

TRUNCATE TABLE is a better solution, if you want to delete all the rows.

CodePudding user response：

Make it a compound condition.

WHERE id IS NULL OR id IS NOT NULL;

Then again, maybe the safety condition is there for an important reason that you should not get around.