MySQL 1175 Error While Using Primary Keys

Question

What would cause the error 1175 that starts out with: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.... The problem I have is that I did include a WHERE clause with key columns: WHERE a.a_id = b.b_id

a_id and b_id are the only key columns in their respective tables.

START TRANSACTION;
    UPDATE table_a a, table_b b
    SET a.update_me = b.update_from_me
WHERE a.a_id = b.b_id;
SELECT * FROM a;

What am I missing?

Answer 1

Well it's quite simple.

You have enabled the safe update mode (as the error stated). Your statement has a where clause, right. But it's interpreted as a JOIN not as a WHERE as you provide a syntax like a INNER JOIN.

Your code:

UPDATE table_a a, table_b b
SET a.update_me = b.update_from_me
WHERE a.a_id = b.b_id;

Is technically the same (and interpreted as such) as this one:

UPDATE a
FROM table_a a
INNER JOIN table_b b
        ON a.a_id = b.b_id;b_id
SET a.update_me = b.update_from_me;

Which means, there is no really WHERE. You update the whole table table_a, which is matching with the other table table_b.

If you really want to achieve this, you can disable the Safe Update Mode for this query using this code:

SET SQL_SAFE_UPDATES=0;
UPDATE table_a a, table_b b
SET a.update_me = b.update_from_me
WHERE a.a_id = b.b_id;

Anyway, your mysql server is running with mysqld --safe-updates or mysqld --i-am-a-dummy which is pretty the same option.

You can check if this is really enabled using one of the following commands:

SELECT @@SQL_SAFE_UPDATES
-- or
SHOW VARIABLES LIKE 'sql_safe_updates'

By the way, you can also try a workaround to get the update done. Safe Update Mode requires a WHERE or a LIMIT. Which will limit the impact of the Update. Another idea can be to use a pretty high LIMIT for your UPDATE.

UPDATE table_a a, table_b b
SET a.update_me = b.update_from_me
WHERE a.a_id = b.b_id
LIMIT 100000;

If the Safe Update Mode is enabled, you need to be aware of DELETE's too. They need both. A WHERE and a LIMIT. See the docs.