INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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.)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close