Advertisement
Advertisement


MySQL error code: 1175 during UPDATE (MySQL-Workbench vs. console)


Question

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

2017/01/17
1
2
1/17/2017 2:44:47 AM


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.

2019/01/15