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

MySQL deleting input data issue

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 and reconnect

I tried deleting a row of data which was user-input (mysql – backend)

How do I delete a row of data?

Tags:

2

Answers


  1. 0

    A specific row of data can be deleted by showing which exact row you want to delete by using a WHERE clause, indicating the specific key of the row

    DELETE FROM xxx WHERE xKEYx = 'yyy' helps you delete a row that contains the ‘yyy’ key element and thus it is safe to use.

    Login or Signup to reply.
  2. 0

    There are at least three solutions:

    1. Use a WHERE clause in your DELETE statement that has a condition to apply the delete only to specific values in key column. A key column must identify the row(s) to delete. So it must be the primary key or unique key of the table.

      DELETE FROM mytable WHERE id = ?;

      Note that even if you do reference a key column in your query, it’s possible that the optimizer will not choose that unique index for some reason. In that case, you still get the error 1175.

    2. Use a LIMIT clause in your DELETE statement. This bypasses the "safe mode" for the current statement. It will delete only a finite number of rows. Though you can use LIMIT with a very large number, even larger than the number of rows in the table.

      DELETE FROM mytable WHERE non_key_column = ? LIMIT 1;

      Using LIMIT with a multi-table DELETE is not supported by MySQL syntax.

    3. Disable safe mode in your session. Then you can do whatever you want in a subsequent DELETE statement in the same session. Even if it results in deleting every row in your table! So make sure you understand the logic of your DELETE statement.

      SET sql_safe_updates=0;
DELETE FROM mytable WHERE id=id; -- obviously true on every row

    For full information, read the documentation on Safe-Updates Mode here: https://dev.mysql.com/doc/refman/8.0/en/mysql-tips.html#safe-updates

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

Back To Top
Search