Update Query with a MAX(Date) subquery
Update Query with a MAX(Date) subquery
(OP)
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
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
Talk To Other Members
RE: Update Query with a MAX(Date) subquery
CODE --> sql
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.)