4

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?

1 Answer 1

6

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.