How would you modify a SQL Update statement to include matching on a field that has the highest value for an int type field?

How would you revise the following update statement to include WHERE
AgeDays (an integer field) that exists in both tables has the highest AgeDays value for tbl_SMR_OpenItemsHistory?

---------------

tbl_SMR_OpenItems.AgeDays
tbl_SMR_OpenItemsHistory.AgeDays
       
----------------

        UPDATE tbl_SMR_OpenItems
         SET tbl_SMR_OpenItems.FootNote = tbl_SMR_OpenItemsHistory.FootNote
         FROM tbl_SMR_OpenItemsHistory WHERE
         (tbl_SMR_OpenItems.Office = tbl_SMR_OpenItemsHistory.Office)
         AND (tbl_SMR_OpenItems.Bank =tbl_SMR_OpenItemsHistory.Bank)
         AND (tbl_SMR_OpenItems.TransDate=tbl_SMR_OpenItemsHistory.TransDate)
         AND (tbl_SMR_OpenItems.ProcessDate=tbl_SMR_OpenItemsHistory.ProcessDate)
         AND (tbl_SMR_OpenItems.T=tbl_SMR_OpenItemsHistory.T)
         AND (tbl_SMR_OpenItems.Type=tbl_SMR_OpenItemsHistory.Type)
         AND (LTRIM(RTRIM(tbl_SMR_OpenItems.Description)) = LTRIM(RTRIM(tbl_SMR_OpenItemsHistory.Description)))
         AND (tbl_SMR_OpenItems.Amount=tbl_SMR_OpenItemsHistory.Amount)

Thus, if the records match for all field values, then select the tbl_SMR_OpenItemsHistory record that has
the highest value for tbl_SMR_OpenItemsHistory.AgeDays
zimmer9Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jason SchlueterIT ManagerCommented:
Does this get you what you need?  It sounds like you only want to update where agedays in both the history and current tables are the same and also match the highest agedays value in the history table.

UPDATE oi
SET    oi.footnote = oih.footnote
FROM   tbl_smr_openitems oi
       JOIN tbl_smr_openitemshistory oih
         ON oi.Bank = oih.Bank
            AND oi.TransDate = oih.TransDate
            AND oi.ProcessDate = oih.ProcessDate
            AND oi.T = tbl_SMR_OpenItemsHistory.T
            AND oi.Type = oih.Type
            AND Ltrim(Rtrim(oi.Description)) = Ltrim(Rtrim(oih.Description))
            AND oi.Amount = oih.Amount
            AND oi.agedays = oih.agedays
WHERE  oih.agedays = (SELECT Max(oih.agedays)
                      FROM   tbl_smr_openitemshistory) 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zimmer9Author Commented:
I tried:

UPDATE oi
        SET oi.footnote = oih.footnote
        FROM tbl_SMR_OpenItems oi
        JOIN  tbl_SMR_OpenItemsHistory oih
        ON oi.Bank = oih.Bank
            AND oi.TransDate = oih.TransDate
            AND oi.ProcessDate = oih.ProcessDate
            AND oi.T = oih.T
            AND oi.Type = oih.Type
            AND Ltrim(Rtrim(oi.Description)) = Ltrim(Rtrim(oih.Description))
            AND oi.Amount = oih.Amount
            AND oi.AgeDays = oih.AgeDays
        WHERE oi.AgeDays = (SELECT Max(oih.AgeDays)
        FROM   tbl_SMR_OpenItemsHistory)

And I get the message:

Number [146] Severity [15] State [1] Server [NYQxxx] Procedure [usp_Calc] Cannot perform an aggregate function on a column from a table not declared in the same subquery.
0
zimmer9Author Commented:
Actually I don't want to match the highest AgeDays in the ENTIRE History table.
If and only if there is more than 1 record that matches on all other fields, then I want the record with the highest AgeDays.
0
zimmer9Author Commented:
The OpenItemsHistory table can have more than 1 record that matches a record from the OpenItems table.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.