MySQL/MariaDB – How to Update a Table Using Summed Values from Another Table

group byMySQLsumupdate

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!!

Best Answer

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.

Related Question