Update Query with a MAX(Date) subquery 1

[jchewsmith]

I am trying to update a row (Order line) in a table with a the MAX(ShipDate) of the other sales order lines in the table.

OrderLine.tbl

SO Line# Date Type Amt
123 1 7/1/15 Item 10.00
123 2 7/2/15 Item 5.00
123 3 7/3/15 Item 7.00

I want to Update the following:

123 4 7/3/15 Fee 1.00 the 7/3 being the max of all other lines in the table current for that sales order

 

[JarlH]

update orderstable t1
  set Amt = (select max(Amt) from orderstable t2
             where t2.SO = t1.SO)
where not exists (select 1 from orderstable t3
                  where t3.SO = t1.SO
                    and t3.Date > t1.Date)

Set Amt to the highest Amt value for that SO (first sub-query), for the rows has no later row in the table with the same SO (second sub-query.)

However, I can't see why you want this kind of logic. The risk of data inconsistency is too high. Have you considered a view instead? (It will always be up to date.)