Update a table using summed values from another table in MySQL/mariaDB

Question

I was trying to use the query below to update the ORDERS table by making the O_TOTALPRICE column up-to-date when the LINEITEM table is modified. And I was using a standard TPC-H database.

UPDATE ORDERS O, LINEITEM L
 SET
 O.O_TOTALPRICE = SUM(L.L_EXTENDEDPRICE * (1 - L.L_DISCOUNT/100) * (1 + L.L_TAX/100))
 WHERE
 L.L_ORDERKEY = O.O_ORDERKEY;

However, this results in 1111 - Invalid use of group function.

---

The operation is like this:

-----------------LINEITEM---------------- ---------ORDERS---------
L_ORDERKEY L_EXTPRICE L_DISCOUNT L_TAX O_ORDERKEY O_TOTALPRICE
========== ========== ========== ===== ========== ============
1 12.99 20 8.5 ┬ 1 <TO CALC>
1 8.99 30 11 ┘
2 99.99 10 10 ┬ 2 <TO CALC>
2 49.99 0 8.5 ┤
2 3.99 50 14.7 ┘

---

Since there are multiple rows in LINEITEM corresponding to the record in ORDERS, I tried to add GROUP BY L.L_ORDERKEY, but that just came to a syntax error.

Is this possible to accomplish in the UPDATE statement, or does it have to be split to multiple statements? Thanks!!

• Rick James: Note how redundant the L_ is, since it is preceded by L..
• Rick James: For syntax error, please show the entire statement, and the "... near '..." of the error message. It always points at the bad spot.
• markp-fuso: I recommend you update the question to include the desired results, ie, what should ORDERS look like after a successful update
• jackxujh: @RickJames The L_ is in the TPC-H DDL file, and I didn't change it. I do agree the L. is so extra, though.
• jackxujh: @RickJames The whole 1111 error message is shown in the question, I wonder why mariaDB didn't tell me which line had the error. In fact, I was using this UPDATE statement in a stored procedure, and the creation is fine,. But when executing the procedure, I get the 1111 error with no indication which part is wrong..

Answer 1

Assumptions:

• L.L_EXTENDEDPRICE should be L.L_EXTPRICE
• L_DISCOUNT and L_TAX are percentages, in which case the values stored in LINEITEM need to first be divided by 100.0 before performing calculations

Some sample data:

drop table if exists LINEITEM;
drop table if exists ORDERS;
create table ORDERS
(O_ORDERKEY int
,O_TOTALPRICE decimal(10,2));
insert into ORDERS values
(1,0.0),
(2,0.0);
create table LINEITEM
(L_ORDERKEY int
,L_EXTPRICE decimal(10,2)
,L_DISCOUNT decimal(10,2)
,L_TAX decimal(10,2));
insert into LINEITEM values
(1,12.99,20, 8.5),
(1, 8.99,30,11),
(2,99.99,10,10),
(2,49.99, 0, 8.5),
(2, 3.99,50,14.7);

Contents of ORDERS before the update:

select * from ORDERS;
O_ORDERKEY O_TOTALPRICE
---------- ------------
 1 0.00
 2 0.00

Proposed update statement:

update ORDERS O
set O.O_TOTALPRICE = (select sum(L.L_EXTPRICE * (1 - L.L_DISCOUNT/100.0) * (1 + L.L_TAX/100.0))
 from LINEITEM L
 where L.L_ORDERKEY = O.O_ORDERKEY);

Contents of ORDERS after the update:

select * from ORDERS;
O_ORDERKEY O_TOTALPRICE
---------- ------------
 1 18.26
 2 155.52

Here's a rextester fiddle of the above.

• jackxujh: Sorry I made a mistake when making the boxed chart. But hopefully it does not affect the main part of the UPDATE statement.
• markp-fuso: not sure I understand your comment; if you've made a typo in the question then I'd suggest you update the question otherwise ... have you tried the proposed update and if so, what happened ... error? wrong answer? correct answer?
• jackxujh: I am sorry for the trouble! I am outside now (using the app on my phone), and I will try the proposed answers once I am back to my computer.
• jackxujh: Yes, this does work! I am very sorry it took me so long to get back to this, and I had you wait for the follow-up this long!
• markp-fuso: no problem, glad I could help

Answer 2

UPDATE ORDERS
 JOIN 
 ( SELECT L_ORDERKEY,
 SUM(L.L_EXTENDEDPRICE * (1 - L_DISCOUNT) * (1 + L_TAX)) AS ep
 FROM LINEITEM 
 GROUP BY L_ORDERKEY
 ) AS j ON j.L_ORDERKEY = ORDERS.O_ORDERKEY
 SET ORDERS.O_TOTALPRICE = j.ep;

If that fails, try this syntax:

UPDATE ORDERS
 SET O_TOTALPRICE = 
 ( SELECT SUM(L.L_EXTENDEDPRICE * (1 - L_DISCOUNT) * (1 + L_TAX))
 FROM LINEITEM
 WHERE L_ORDERKEY = ORDERS.O_ORDERKEY 
 )

• jackxujh: Will join operation take more time?
• Rick James: The JOIN will probably be faster, since it is computing all the new values at once. The subquery with the SUM is probably slower.
• markp-fuso: @jackxujh: compare query plans and execution metrics to know for sure which is slower/faster (assuming there is in fact a difference); a lot will depend on how the query engine decides to parse/compile the query
• jackxujh: Both the ORDERS and LINETIEM tables actually have many more columns, and foreign keys. And ORDERS is referenced by LINEITEM. ( also a TPC-H thing) But the O_TOTALPRICE is not not referenced nor a prime key. Considering all these, will the JOIN still faster?
• Rick James: @markp - The query plan won't show the whole picture. Table size and bulkiness of the columns play a factor.