Update Query With SUM

Question

How would I update my table using this update statement? Note - this is sample DDL only, actual table structure does not have a calculated field like this

Declare @orderinfo Table
(
    ordernumber varchar(10)
   ,ordersalesperson varchar(100)
   ,orderfromstore varchar(50)
   ,orderTotal decimal(16,4)
)

Declare @orderline Table
(
  ordernumber varchar(10)
  ,orderitemquantity int
  ,orderitemprice decimal(16,4)
)

Insert Into @orderinfo (ordernumber, ordersalesperson, orderfromstore) VALUES
('ABC123', 'Sales Person A', 'Store 1')

Insert Into @orderline Values
('ABC123', '2', '3.75'), ('ABC123', '4', '5.25'), ('ABC123', '2', '7.48')

UPDATE oi
SET orderTotal = SUM(ol.orderitemquantity * ol.orderitemprice)
From @orderinfo oi
Join @orderline ol
On oi.ordernumber = ol.ordernumber

Answer 1

Try one of possibilities:

update oi
set ordertotal = (select ISNULL(SUM(orderitemquantity * orderitemprice), 0) from @orderline where ordernumber = oi.ordernumber) 
from @orderinfo oi

Answer 2

For future reference, you should include the complete error message you encountered (but after having searched the internet for it). It is a common error and there are many discussions you can find which suggest various approaches. Have you considered a view (materialized or not) that will always have the correct value? Or perhaps a trigger? Below is one example:

with cte as (select ol.ordernumber, 
     sum(ol.orderitemquantity * ol.orderitemprice) as totalord 
     from  @orderline as ol group by ol.ordernumber )
 update oi set orderTotal = cte.totalord
 from @orderinfo as oi inner join cte 
 on oi.ordernumber = cte.ordernumber;