Question 21160 in Mysql Question posted in
The official MySQL documentation can be found here.

SQL WHERE Clause for any value? – Mysql

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.

Tags:

3

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search