4

I have a particular MySQL UPDATE statement which does specify the required primary key in its WHERE clause and yet which still produces Error 1175 when run in MySQL Workbench.

I am perfectly aware of MySQL error code: 1175 during UPDATE in MySQL Workbench. My case appears to be the same as MySQL error code: 1175 during UPDATE (MySQL-Workbench vs. console). Like that questioner, I do not wish to disable MySQL-Workbench's "safe update/delete" option. That question failed to get a solution. I would like to try to get an actual solution.

SQL UPDATE statement:

-- update new columns' values from corresponding rows in `charges_arc` UPDATE `charges` INNER JOIN `charges_arc` ON `charges`.`ChargeID` = `charges_arc`.`ChargeID` SET `charges`.`ChargeClearDate` = `charges_arc`.`ChargeClearDate` WHERE `charges`.`ChargeID` = `charges_arc`.`ChargeID`;

ChargeID is indeed the Primary Key column in both charges and charges_arc tables.

This means that this statement does satisfy MySQL Workbench's 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.

Is there a solution to rewrite this query such that Workbench does not Error 1175, and which does not require setting SET SQL_SAFE_UPDATES=0/changing Workbench's preferences?

JonBrave
  • 4,045
  • 3
  • 38
  • 115
  • 1
    Interesting. Perhaps the issue is that the join in your update widens the number of records which could be deleted, i.e. just specifying a primary key is not "safe" anymore because it could still in theory include a large chunk of the table. – Tim Biegeleisen Mar 16 '18 at 10:50
  • @TimBiegeleisen That in turn is an interesting theory. I was thinking of trying rewriting it to not use `JOIN` but instead change to sub-query `WHERE charges.ChargeID IN ( SELECT charges_arc.ChargeID FROM charges_arc WHERE charges_arc.ChargeID = charges.ChargeID )`, but if your theory is correct it might fall foul there too. Since I've typed this in, I'll give it a go now... – JonBrave Mar 16 '18 at 11:02
  • Whoops, you can't access `charges_arc`.`ChargeClearDate` in the `SET` clause if you use the sub-query idea... – JonBrave Mar 16 '18 at 11:11

2 Answers2

0

Well, having played further, so far I have found that the following seems to keep Workbench happy:

-- update new columns' values from corresponding rows in `charges_arc` UPDATE `charges` INNER JOIN `charges_arc` ON `charges`.`ChargeID` = `charges_arc`.`ChargeID` SET `charges`.`ChargeClearDate` = `charges_arc`.`ChargeClearDate` WHERE `charges`.`ChargeID` = `charges_arc`.`ChargeID` AND `charges`.`ChargeID` <> -9999

That's just adding AND charges.ChargeID <> -9999 to the condition. It hardly narrows the scope much(!), and it's pretty ugly(!). I can only guess that Workbench would like to "see some kind of literal test against the PK", so that you show it you have thought about the PK in a certain way! It does at least allow you to do the query without disabling "safe updates".

I will leave this open for a couple of days to see if someone can think of something neater.

For my own part, I have a lot of these kind of UPDATEs in a large upgrading script file, this looks so ugly to me that I may end up going for SET SQL_SAFE_UPDATES=0 over the whole file after all...

EDIT: In the end I decided it was so ugly having to add something like the extra AND clause above to these types of UPDATE ... JOIN ...s that I preferred to SET SQL_SAFE_UPDATES=0 around them, at least for clarity.

JonBrave
  • 4,045
  • 3
  • 38
  • 115
0

Using MySQL 5.6 and MySQLWorkbench 8, I received this error in the same circumstances. I was able to fix the error by qualifying the field name in the WHERE clause.

For example, this caused the 1175 error:

UPDATE `tReports`
SET
`Title` = Title,
`Descr` = Descr
WHERE `ID` = ID;

And this resolved it:

UPDATE `tReports`
SET
`Title` = Title,
`Descr` = Descr
WHERE `tReports`.`ID` = ID;
pzzd
  • 111
  • 1
  • 7