// MySQL error reference

MySQL error 1175: Safe update mode blocked the statement

sql_safe_updates is on, and the UPDATE or DELETE has no WHERE on a key column and no LIMIT, so MySQL refuses to change possibly every row.

Error number
1175
Name
ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
SQLSTATE
HY000
Category
Server limits and settings
Error message
You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. %s

What it means

With sql_safe_updates = 1 (MySQL Workbench turns it on by default, and so does mysql --safe-updates), MySQL rejects UPDATE and DELETE statements that don't filter on a key column or use LIMIT. It's a guard against accidentally changing a whole table.

Common causes

  • An UPDATE or DELETE without WHERE.
  • A WHERE on a column that isn't indexed, so MySQL can't prove it is selective.
  • Running in MySQL Workbench, which enables safe updates by default.

How to fix it

  1. Filter on a key column, or add a LIMIT.

    UPDATE customers SET status = 'inactive' WHERE id IN (17, 42);
  2. If you really mean to change every matching row, turn the guard off for the statement and back on afterwards.

    SET SQL_SAFE_UPDATES = 0;
    UPDATE customers SET status = 'inactive' WHERE last_login < '2020-01-01';
    SET SQL_SAFE_UPDATES = 1;

Related errors

More server limits and settings errors

The code and message text are from the MySQL 8.4 Error Message Reference. Browse the full MySQL error list or search every database error.

// fewer errors, faster fixes

Work on MySQL in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for MySQL and the other databases you use.