MySQL only update rows if where clause is present

Question

I believe I read somewhere that there is a mysql configuration setting that prevents rows from being updated via the console unless there is a WHERE clause present.

I haven't been able to find it in the documentation, does that option exist?

Answer 1

That is MYSQL SAFE UPDATE MODE.

Check here.

And also the --safe-updates option.

Answer 2

Just SET SQL_SAFE_UPDATES=0; before running your query.

See MySQL tips regarding safe mode.

For beginners, a useful startup option is --safe-updates (or --i-am-a-dummy, which has the same effect). It is helpful for cases when you might have issued a DELETE FROM tbl_name statement but forgotten the WHERE clause. Normally, such a statement deletes all rows from the table. With --safe-updates, you can delete rows only by specifying the key values that identify them. This helps prevent accidents.

You are not permitted to execute an UPDATE or DELETE statement unless you specify a key constraint in the WHERE clause or provide a LIMIT clause (or both). For example:

UPDATE tbl_name SET not_key_column=val WHERE key_column=val;

UPDATE tbl_name SET not_key_column=val LIMIT 1;

Answer 3

From the MySQL docs: http://dev.mysql.com/doc/refman/5.5/en/mysql-tips.html

"4.5.1.6.2. Using the --safe-updates Option

For beginners, a useful startup option is --safe-updates (or --i-am-a-dummy, which has the same effect). It is helpful for cases when you might have issued a DELETE FROM tbl_name statement but forgotten the WHERE clause. Normally, such a statement deletes all rows from the table. With --safe-updates, you can delete rows only by specifying the key values that identify them. This helps prevent accidents.

[...]

You are not permitted to execute an UPDATE or DELETE statement unless you specify a key constraint in the WHERE clause or provide a LIMIT clause (or both)."