MySQL Error Code: 1175 during UPDATE with a WHERE clause

Question

I am trying to update the total of Payment to the total of price from Packages where the transaction numbers are the same. However MySQL keeps giving me the error:

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

Here is the command:

UPDATE Payment py
SET total = (SELECT SUM(price) FROM Package pk
             WHERE py.transactionNo = pk.transactionNo);

I would think that I am using a WHERE that uses a KEY column (transactionNo is the primary key for Payment). Am I simply writing this wrong?

Does this answer your question? [MySQL error code: 1175 during UPDATE in MySQL Workbench](https://stackoverflow.com/questions/11448068/mysql-error-code-1175-during-update-in-mysql-workbench) — Nico Haase, Nov 26 '21 at 16:51
The error message tells you that you are not using a key column to update a single row from `py`. The `WHERE` clause is not part of the `UPDATE` query itself, but of a sub query — Nico Haase, Nov 26 '21 at 16:52
Is there any logic why you want to save a "total" that is a calculated field? We don't save that data. — Leandro Bardelli, Nov 26 '21 at 16:55
@LeandroBardelli The initial value for total is 0, but once I add all the packages for a payment, I then want to update it with the total price of all these packages — JJBeaudry, Nov 26 '21 at 17:13
@JJBeaudry I understand, my question is related about why indeed is that field necessary at all? — Leandro Bardelli, Nov 26 '21 at 17:14
@LeandroBardelli Ideally I suppose it isn't necessary, but it is for a school project where I need "Interesting UPDATE statements". So if this query doesn't make sense because of not saving calculated fields I will just have to try something else — JJBeaudry, Nov 26 '21 at 17:20
@JJBeaudry oh, sorry!!! I understand now. Definitely is interesting. Not applicable in real life except if you want to save and history of the value changes, but it's 100% fine. Sorry. — Leandro Bardelli, Nov 26 '21 at 17:23
@LeandroBardelli No problem! What you are saying definitely makes sense in a real life application — JJBeaudry, Nov 26 '21 at 17:28

score 2 · Answer 1 · edited Mar 14 '23 at 11:19

2

You have no a where condition for update..

In this case you could use a proper updated with join using a subquery

UPDATE Payment py
INNER JOIN (
    SELECT pk.transactionNo, SUM(price) sum_price
    FROM Package pk
    GROUP BY pk.transactionNo
    WHERE py.transactionNo = pk.transactionNo
) t on  py.transactionNo = t.transactionNo

SET py.total =t.sum_price

edited Mar 14 '23 at 11:19

Tushar

3,527
9
27
49

answered Nov 26 '21 at 17:07

ScaisEdge

131,976
10
91
107

MySQL Error Code: 1175 during UPDATE with a WHERE clause

1 Answers1