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

2
votes

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

3
mysqlsql-updatemysql-workbench
Pls share the results of the explain for the delete in both environments and the indexes defined on both tables. – Shadow
Quick clarification - delete what? One of the tables? Both of the tables? One of the columns? – djmac

3 Answers

2
votes

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;
1
votes

If you are using workbech, you can first execute

SET SQL_SAFE_UPDATES = 0;

And then execute delete statement

0
votes

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.