How to fix Table is specified twice in MySQL 8.2?

Question

Here is my query:

UPDATE order_product
                SET amount = coalesce(
                    (
                        SELECT amount_origin + (SELECT sum(amount_to_change)
                                                 FROM order_product_edits 
                                                 WHERE order_product_id = NEW.order_product_id
                     )
                        FROM order_product
                        WHERE id = NEW.order_product_id
                    )
                , 0)

It works well on my local computer (MySQL 8.3) .. but it throws this error on the server (MySQL 8.2):

Table is specified twice, both as a target for 'UPDATE' and as a separate source for data in mysql

Any idea how can I fix it?

Possible duplicate of [Table is specified twice, both as a target for 'UPDATE' and as a separate source for data in mysql](https://stackoverflow.com/questions/44970574/table-is-specified-twice-both-as-a-target-for-update-and-as-a-separate-source) — DDS, Jan 23 '19 at 14:10
"It works well on my local computer (MySQL 10.3) .. but it throws this error on the server (MySQL 10.2):" MySQL 10? MySQL's max version is 8! You probably mean MariaDB 10 here.. — Raymond Nijland, Jan 23 '19 at 14:15
Are you using trigger code? The `NEW.` syntax suggests you are using triggers.. If so post the complete trigger code.. if not your syntax is plain wrong and then i advice you to read [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Raymond Nijland, Jan 23 '19 at 14:21

score 1 · Answer 1 · answered Jan 23 '19 at 15:06

The problem is the nested correlation clause. I think you don't need it:

UPDATE order_product op
    SET amount = COALESCE(op.amount_origin + (SELECT sum(ope.amount_to_change)
                                             FROM order_product_edits ope
                                             WHERE ope.order_product_id = op.order_product_id
                                            ), 0
                         )
     WHERE op.id = NEW.order_product_id;

I am surprised the default value is 0 rather than op.amount_origin.

score 0 · Answer 2 · answered Jan 23 '19 at 14:22

Coming from the T-sql world, I would suggest that you make use of a temp table or table variable, such that you can store something in the idea of key, updated value and once you have that populated you can use that within your update query to set the values to the updated values.

How to fix Table is specified twice in MySQL 8.2?

2 Answers2