How to Fix MySQL Error 1175: Safe Update Mode Blocks UPDATE/DELETE Without WHERE

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column is raised when a UPDATE or DELETE statement is executed without a WHERE clause that references a key column while SQL_SAFE_UPDATES is enabled. The error symbol is ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE.

Impact

The statement is rejected entirely and no rows are modified. This is a client-side safeguard, not a server-enforced constraint — the session variable SQL_SAFE_UPDATES=1 tells the MySQL client (or server session) to block any UPDATE or DELETE that could affect an unbounded set of rows. The transaction is not rolled back; the statement simply never executes.

Developers most commonly encounter this in MySQL Workbench, which enables safe update mode by default for every new connection. It also surfaces when application code or deployment scripts run against a MySQL server that has SQL_SAFE_UPDATES=1 set in my.cnf or the connection string, and can appear unexpectedly when migrations or ORM bulk-update operations omit a key-based predicate.

Common Causes

Running UPDATE or DELETE without any WHERE clause. The simplest case — a statement like UPDATE orders SET status = 'archived' affects every row, which safe mode blocks unconditionally.
WHERE clause present but does not reference a key column. Safe mode requires the WHERE predicate to filter on an indexed key (primary key or unique index). Filtering on a non-indexed column such as WHERE status = 'pending' is not sufficient, even though it does narrow the result set.
MySQL Workbench default connection setting. Workbench enables SQL_SAFE_UPDATES automatically for each connection under Preferences > SQL Editor. Developers switching from another client (DBeaver, the CLI, an application) may be surprised to find their queries fail only in Workbench.
Server-side configuration in my.cnf. A DBA may have added sql_safe_updates=ON to [mysqld] in the server configuration, applying the restriction to all sessions unless explicitly overridden per-connection.
ORM bulk updates without a primary-key predicate. Frameworks like SQLAlchemy, ActiveRecord, or Hibernate sometimes generate UPDATE statements filtered on non-key columns during mass-update operations (e.g., update_all(status: 'active') on a non-indexed column), which fails under safe mode.

Troubleshooting and Resolution Steps

Check whether safe update mode is active in the current session:
```
SHOW VARIABLES LIKE 'sql_safe_updates';
```

Disable safe update mode for the current session only (preferred approach — does not affect other connections):

SET SQL_SAFE_UPDATES = 0;
UPDATE orders SET status = 'archived' WHERE created_at < '2023-01-01';
SET SQL_SAFE_UPDATES = 1;

Rewrite the query to use a primary key or unique index in the WHERE clause. This is the safest long-term fix and satisfies safe mode without disabling it:

-- Instead of filtering on a non-key column:
UPDATE orders SET status = 'archived' WHERE status = 'old';  -- blocked

-- Use a subquery or join to resolve to primary keys first:
UPDATE orders SET status = 'archived'
WHERE id IN (SELECT id FROM (SELECT id FROM orders WHERE status = 'old') AS t);

The nested subquery trick forces MySQL to materialize the key set before the update, which satisfies the safe-mode key requirement.

Use LIMIT as an alternative workaround. SQL_SAFE_UPDATES also allows statements that include a LIMIT clause, which caps the maximum rows affected:
```
UPDATE orders SET status = 'archived' WHERE status = 'old' LIMIT 1000;
```
Disable safe update mode in MySQL Workbench permanently (for local development convenience): go to Edit > Preferences > SQL Editor and uncheck "Safe Updates (rejects UPDATEs and DELETEs with no restrictions)". Restart the connection.
Remove the server-side setting if it was applied globally and is not intentional:
```
-- Check current global value
SHOW GLOBAL VARIABLES LIKE 'sql_safe_updates';

-- Override for the current session without changing the global config
SET SESSION SQL_SAFE_UPDATES = 0;
```
To remove the global default, edit my.cnf / my.ini, remove or comment out sql_safe_updates=ON, and restart the server.

Additional Information

The SQLSTATE code HY000 is a generic "general error" class used by MySQL for many server-specific conditions that do not map to a standard SQLSTATE value.
SQL_SAFE_UPDATES is a session variable and can be set per connection without affecting other clients.
Safe update mode also blocks DELETE FROM table_name with no WHERE clause. The protection applies to both UPDATE and DELETE statements.
This setting is unrelated to MySQL's strict SQL mode (STRICT_TRANS_TABLES, STRICT_ALL_TABLES) — those govern data validation on INSERT/UPDATE values, not query structure.
In MySQL 8.0+, the behavior of SQL_SAFE_UPDATES is unchanged from 5.7. It remains a session-level variable only and cannot be set as a system variable in a running server without configuration file changes.
The --safe-updates (or --i-am-a-dummy) CLI flag enables this mode when connecting via the mysql command-line client, producing the same error.

Frequently Asked Questions

Why does my query work in my application but fail in MySQL Workbench? MySQL Workbench enables SQL_SAFE_UPDATES=1 by default for each new connection. Your application's database driver likely does not set this variable, so the restriction only applies in Workbench. You can disable it under Edit > Preferences > SQL Editor or by running SET SQL_SAFE_UPDATES = 0 at the start of a Workbench session.

Is it safe to disable SQL_SAFE_UPDATES in production? In production applications, safe update mode is typically not set at the application level — it is a developer convenience guard. Disabling it per-session for a well-tested migration or maintenance script is safe. However, think carefully before removing it from a server-wide configuration: it serves as a last line of defense against accidental mass updates or deletes.

Does adding LIMIT satisfy safe update mode? Yes. MySQL's safe update mode permits statements that include a LIMIT clause, in addition to those with a key-based WHERE predicate. DELETE FROM logs WHERE created_at < NOW() LIMIT 500 will succeed even without a key column in the WHERE clause.

Why does filtering on a non-indexed column not satisfy safe mode? SQL_SAFE_UPDATES is specifically designed to prevent full-table modifications. A WHERE clause on a non-key column still requires a full table scan and could modify every row if the predicate matches all rows. Safe mode requires a key column predicate precisely because it guarantees MySQL can target rows by index rather than scanning the entire table.