MySQL error code: 1175 during UPDATE (MySQL-Workbe

问题:

Am very aware of that this issue can be resolved with disabling safe update mode enabled (e.g. see here: MySQL error code: 1175 during UPDATE in MySQL Workbench). However, I do not wish to disable safe update mode (and there are many many solutions that propose this).

Similarly, I am aware that setting the WHERE clause to KEY-value that matches everything is supposed to work. However, doesn't appear to work on mysql-workbench - at least not the way I hoped (or the way it did work on the console).

For example, the following didn't work on mysql-workbench (but did on the console):

UPDATE FUEL_SOURCES AS FS
INNER JOIN
    FUEL_CATEGORY FC ON FC.FUEL_CATEGORY = FS.FUEL_CATEGORY 
SET 
    FS.FUEL_CATEGORY_ID = FC.ID
WHERE
    FC.ID <> 0 AND FS.ID <> 0

...If I explicitly / exactly set the ID's (e.g. WHERE FC.ID = 20 AND FS.ID <> 10 for example) it would work in mysql-workbench. But doing this would involve iterating through every key-pair combination.

Be intereted to know what is causing this behaviour, or if I am doing something horribly wrong. Using mysql-workbench 6.3

回答1:

From https://dev.mysql.com/doc/workbench/en/workbench-faq.html#faq-workbench-delete-safe

By default, Workbench is configured to not execute DELETE or UPDATE queries that do not include a WHERE clause on a KEY column.

Such configuration prevents you from deleting or updating table mistakenly, since you are doing a batch update on data without a key.

To resolve this, as you may be already aware the following options.

1. Open your Workbench Preferences, select the SQL Editor section, and disable the following preference: "Safe Updates" - Forbid UPDATEs and DELETEs with no key in WHERE clause or no LIMIT clause.
2. Run SET SQL_SAFE_UPDATES=0;

回答2:

If you want to still update your data with safe update on, you must retool your where clause so that it includes references to the table's primary key(s). See this page.