MySQL error code: 1175 during UPDATE in MySQL Workbench
Resolving MySQL Error 1175: Safe Update Mode in MySQL Workbench

Understand and fix MySQL Error Code 1175, 'You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column', when performing UPDATE or DELETE operations in MySQL Workbench.
MySQL Error Code 1175 is a common hurdle for developers and database administrators, especially when working with UPDATE
or DELETE
statements in MySQL Workbench. This error, 'You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column', is a safety mechanism designed to prevent accidental mass updates or deletions on tables without specifying a primary key or unique index in the WHERE
clause. While it's a valuable safeguard, it can interrupt your workflow if you're not aware of its implications. This article will explain why this error occurs, how MySQL's safe update mode works, and provide several methods to resolve it, ensuring you can perform your database operations safely and efficiently.
Understanding MySQL's Safe Update Mode
MySQL's safe update mode, officially known as SQL_SAFE_UPDATES
, is a session-specific setting that prevents UPDATE
and DELETE
statements from executing if they don't include a WHERE
clause that references a key column (primary key or unique index). This feature is enabled by default in MySQL Workbench and aims to protect users from inadvertently modifying or deleting large numbers of rows, which could lead to data loss or corruption. Without this mode, a simple typo in a WHERE
clause could turn a targeted update into a full table modification.
flowchart TD A[Execute UPDATE/DELETE Statement] --> B{Is SQL_SAFE_UPDATES ON?} B -- Yes --> C{Does WHERE clause use KEY column?} C -- No --> D["Error 1175: Safe Update Mode"] C -- Yes --> E[Execute Statement Successfully] B -- No --> E
Flowchart illustrating the logic of MySQL's Safe Update Mode
Common Scenarios Leading to Error 1175
Error 1175 typically arises in a few common situations:
- Missing
WHERE
Clause: Attempting toUPDATE
orDELETE
all rows in a table without anyWHERE
clause. - Non-Key
WHERE
Clause: Using aWHERE
clause that filters by a non-key column (a column that is neither a primary key nor part of a unique index). - Complex
WHERE
Clause: Even if a key column is involved, if theWHERE
clause is too complex or involves functions that prevent MySQL from identifying the key usage, the error might still occur.
Let's look at an example of a query that would trigger this error.
UPDATE products
SET price = price * 1.10
WHERE category = 'Electronics';
Example of an UPDATE statement that might trigger Error 1175 if 'category' is not a key column.
SQL_SAFE_UPDATES
should be done with caution. Always double-check your WHERE
clause before executing UPDATE
or DELETE
statements, especially in production environments, to prevent unintended data modifications.Resolving Error 1175
There are several ways to address Error 1175, ranging from modifying your query to temporarily disabling the safe update mode. The best approach depends on your specific needs and the context of your operation.
1. Method 1: Use a Key Column in the WHERE Clause
The most recommended and safest way to resolve Error 1175 is to ensure your UPDATE
or DELETE
statement's WHERE
clause uses a primary key or a unique index column. This aligns with the purpose of safe update mode.
Example: If product_id
is the primary key:
UPDATE products
SET price = price * 1.10
WHERE product_id IN (101, 105, 203);
2. Method 2: Temporarily Disable SQL_SAFE_UPDATES
If you are certain about your UPDATE
or DELETE
operation and understand the risks, you can temporarily disable SQL_SAFE_UPDATES
for your current session. This is often done when you need to update rows based on non-key columns or perform a mass update.
Steps:
- Execute
SET SQL_SAFE_UPDATES = 0;
to disable the mode. - Execute your
UPDATE
orDELETE
statement. - Execute
SET SQL_SAFE_UPDATES = 1;
to re-enable the mode for safety.
SET SQL_SAFE_UPDATES = 0;
UPDATE products
SET price = price * 1.10
WHERE category = 'Electronics'; -- Now this will work
SET SQL_SAFE_UPDATES = 1;
3. Method 3: Configure MySQL Workbench Preferences
For a more permanent (but still session-specific) solution within MySQL Workbench, you can adjust the preferences. This will disable SQL_SAFE_UPDATES
for all new query tabs opened in that Workbench session.
Steps:
- Go to
Edit
>Preferences
(on Windows/Linux) orMySQL Workbench
>Preferences
(on macOS). - Navigate to
SQL Editor
. - Uncheck the option
"Safe Updates" (rejects UPDATEs and DELETEs with no restrictions)
. - Click
OK
and restart MySQL Workbench or open a new query tab for the change to take effect.
Remember, this setting only applies to your local Workbench instance and does not affect the MySQL server itself.
SQL_SAFE_UPDATES
, it's a good practice to wrap your UPDATE
or DELETE
statements within a transaction (START TRANSACTION; ... COMMIT;
or ROLLBACK;
) if your table engine supports it (e.g., InnoDB). This allows you to revert changes if something goes wrong.Best Practices
While disabling safe update mode can be convenient, adopting best practices will help you avoid Error 1175 and maintain data integrity:
- Always use a
WHERE
clause: Even when disabling safe updates, always specify aWHERE
clause to target only the rows you intend to modify. - Backup your data: Before performing any large-scale
UPDATE
orDELETE
operations, especially after disabling safe updates, ensure you have a recent backup of your database. - Test on a development environment: Always test your SQL queries on a development or staging environment before applying them to production.
- Understand your schema: Be familiar with your table's primary keys and unique indexes. This knowledge is crucial for writing efficient and safe queries.