sql - Update a column based on ordered values from another table

Question

I need to update a flag in a table called AccountBase when related table SoldItems has the same uniqueidentifier, but the hard part is, I need to find the latest product sold and compare its SellDatetime with the current time as well. When the latest product sold in 6 years I want set the flag to 1, otherwise 0.

There is 1:N connection between AccountBase and SoldItems, multiple items can be sold to one account, so I need to order it by SellDatetime in SoldItems.

Here is my attempt which is not setting it correctly, but the inner query in exists returns the proper value:

UPDATE acc 
   SET acc.SoldInLastSixYears = CASE WHEN DATEDIFF(DAY, hk.SellDatetime, GETDATE())/ 365.2425 >= 6 THEN 0
            ELSE 1
            END
FROM [AccountBase] acc INNER JOIN SoldItems hk ON acc.AccountId=hk.Owner
where EXISTS(select top(1) SellDatetime From SoldItems where 
SellDatetime is not null AND Owner is not null 
AND Owner=acc.AccountId   order by SellDatetime desc) 

When I try to use the inner query value there is an error I sql did not recognised 'hk':

UPDATE acc 
   SET acc.SoldInLastSixYears = CASE WHEN DATEDIFF(DAY, hk.SellDatetime, GETDATE())/ 365.2425 >= 6 THEN 0
            ELSE 1
            END
FROM [AccountBase] acc 
where EXISTS(select top(1) SellDatetime From SoldItems hk where 
SellDatetime is not null AND Owner is not null 
AND Owner=acc.AccountId   order by SellDatetime desc) 

Please someone help me, how it is should be done?

Answer 1

As far as I understood your problem, you want the latest record of the sellitem.

You can use correlated query with NOT EXISTS as follows:

UPDATE acc 
   SET acc.SoldInLastSixYears 
       = CASE WHEN DATEDIFF(DAY, hk.SellDatetime, GETDATE())/ 365.2425 >= 6 THEN 0
              ELSE 1
         END
FROM [AccountBase] acc INNER JOIN SoldItems hk ON acc.AccountId=hk.Owner
where NOT EXISTS(select 1 From SoldItems s where 
s.Owner=acc.AccountId and s.SellDatetime > hk.SellDatetime)