CODEWITHSUNDEEP
mysql
deb
deb

Reputation: 12814

MySQL only update rows if where clause is present

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?

Upvotes: 14

Views: 5858

Answers (3)

xdazz
xdazz

Reputation: 160833

That is MYSQL SAFE UPDATE MODE.

Check here.

And also the --safe-updates option.

Upvotes: 13

kermit
kermit

Reputation: 1641

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)."

Upvotes: 2

Kermit
Kermit

Reputation: 34055

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;

Upvotes: 8

Related Questions