Quick Work Around for the “You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column” MySQL Error

Quick Work Around for the “You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column” MySQL Error

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]

Bob McKay

About Bob McKay

Bob is a Founder of Seguro Ltd, a full time father and husband, part-time tinkerer-with-wires, coder, Muay Thai practitioner, builder and cook. Big fan of equality, tolerance and co-existence.

Disclosure Policy

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.