Every now and then when I’m working on a MySQL database and trying to manually update records, my update query gets knocked back with the following error (or something similar depending on if I’m working via MySQL WorkBench, etc.):
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 -> Query Editor and reconnect.
This simply means that as a precaution MySQL is refusing to execute the update because it’s potentially too ‘sweeping’ as it doesn’t specifically reference the ID column of the target table.
The follow for example would not execute:
UPDATE store_products SET productCategoryID = 21 WHERE productName LIKE '%dvd%';
A quick and simple way around it I use is to put a condition in the where clause on the ID column that is always true, for example:
UPDATE store_products SET productCategoryID = 21 WHERE productID <> 0 AND productName LIKE '%dvd%';
Hopefully this is a useful timesaver for others too!
For anyone looking for more advanced tips and tricks for MySQL, I thoroughly recommend the [easyazon_link asin=”1449374026″ locale=”US” new_window=”yes” nofollow=”default” tag=”bomc-21″ add_to_cart=”default” cloaking=”default” localization=”default” popups=”no”]MySQL Cookbook![/easyazon_link]
