ERROR 1175 occurs when safe update mode is enabled and you try to UPDATE or DELETE rows without specifying a WHERE clause on a key column. Disable safe updates, add a proper WHERE clause, or use LIMIT to fix this.
ERROR 1175 is a safety mechanism in MySQL that prevents accidental updates or deletions of entire tables. When SQL_SAFE_UPDATES mode is enabled (common in MySQL Workbench and some client tools), MySQL requires that UPDATE and DELETE statements must either: 1. Include a WHERE clause that references a key column (primary key or unique index) 2. Include a LIMIT clause to restrict the number of rows affected 3. Have safe updates mode explicitly disabled This error is intentionally restrictive to protect you from data loss. Without this protection, a forgotten WHERE clause could silently delete or update every row in a table.
Run this query to see the current setting:
SHOW VARIABLES LIKE 'sql_safe_updates';If the value is ON or 1, safe updates are enabled.
Modify your UPDATE or DELETE statement to include a WHERE clause that filters by a primary key or unique index:
-- Instead of this (will fail):
UPDATE users SET status = 'active';
-- Use this (specifies key column):
UPDATE users SET status = 'active' WHERE id = 123;
-- Or for multiple rows:
UPDATE users SET status = 'active' WHERE department_id = 5;Replace id with your table's primary key column name.
If you need to update rows without a specific key condition, add a LIMIT clause:
-- Update the first 100 unprocessed records
UPDATE tasks SET processed = 1 WHERE status = 'pending' LIMIT 100;
-- Delete old archived records
DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY) LIMIT 1000;LIMIT restricts the number of rows affected, preventing accidental mass updates.
If you absolutely need to update all rows, disable safe mode for that query:
SET SQL_SAFE_UPDATES = 0;
-- Now you can run your UPDATE or DELETE without restrictions
UPDATE users SET status = 'active';
DELETE FROM old_data;
-- Re-enable it immediately afterward
SET SQL_SAFE_UPDATES = 1;This disables the safety check only for your current session.
If you're using MySQL Workbench and want to disable safe updates permanently:
1. Go to Edit → Preferences (Windows) or MySQL Workbench → Preferences (Mac)
2. Click the SQL Editor tab
3. Uncheck "Safe Updates (reject UPDATEs and DELETEs with no restrictions)"
4. Click OK and reconnect to the database
Note: This disables the protection globally for all your queries in Workbench, so be extra careful.
When to keep safe updates enabled (recommended):
Safe updates mode is a valuable safeguard, especially in production environments. Most experienced developers keep it enabled and write proper WHERE clauses. This prevents the catastrophic mistake of running DELETE FROM users; when you meant DELETE FROM users WHERE id = 123;.
Optimizer ignoring indexes:
In rare cases, MySQL may refuse to use an index even if a key column is in your WHERE clause. This happens when:
- The estimated memory usage exceeds range_optimizer_max_mem_size (causes table scan instead of index scan)
- The optimizer estimates the scan would examine too many rows
In these cases, you can use LIMIT or explicitly disable safe updates for that query.
Comparison with other databases:
PostgreSQL doesn't have built-in safe update mode, but it's good practice to always use WHERE clauses regardless of database. Other tools like pgAdmin may offer similar safety features.
Safe updates in production:
Enable SQL_SAFE_UPDATES in all production environments and scripts. The slight inconvenience of writing explicit WHERE clauses is worth the protection against data loss.
ERROR 1064: You have an error in your SQL syntax
How to fix "ERROR 1064: You have an error in your SQL syntax" in MySQL
ERROR 1054: Unknown column in field list
Unknown column in field list
ER_WINDOW_RANGE_FRAME_NUMERIC_TYPE (3589): RANGE frame requires numeric ORDER BY expression
RANGE frame requires numeric ORDER BY expression in MySQL window functions
CR_ALREADY_CONNECTED (2058): Handle already connected
How to fix "CR_ALREADY_CONNECTED (2058): Handle already connected" in MySQL
ER_WINDOW_DUPLICATE_NAME (3591): Duplicate window name
How to fix ER_WINDOW_DUPLICATE_NAME (3591) in MySQL window functions