UPDATE and Safe Updates

UPDATE and the Safe-Updates Guard

UPDATE changes every row its WHERE matches, and without a WHERE, every row in the table. Assignments may use the current value, as in this 10% rise for the Databases category:

Matched versus changed, and safe-updates modeSQL

UPDATE products SET price = price * 1.10 WHERE category_id = 3;
UPDATE products SET price = 48.95 WHERE sku = 'BK-SQL-01';      -- already 48.95
SET SESSION sql_safe_updates = 1;
UPDATE products SET stock = stock + 10 WHERE stock < 10;
UPDATE products SET stock = stock + 10 WHERE stock < 10 ORDER BY stock LIMIT 2;

Output

Query OK, 2 rows affected (0.009 sec)
Rows matched: 2  Changed: 2  Warnings: 0
Query OK, 0 rows affected (0.000 sec)
Rows matched: 1  Changed: 0  Warnings: 0
Query OK, 0 rows affected (0.000 sec)
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a
  WHERE that uses a KEY column.
Query OK, 2 rows affected (0.008 sec)
Rows matched: 2  Changed: 2  Warnings: 0

"Rows affected" counts rows that actually changed, not rows matched, and PDO reports the same number, so never read 0 as "no such row". Safe-updates mode rejects an UPDATE or DELETE that has no LIMIT and no key column in its WHERE. Start the client with mysql --safe-updates (or put safe-updates under [mysql] in your option file) and it also caps unbounded SELECTs at 1,000 rows. Applications rely on transactions (Transactions and Locking) instead.

MENU