SQL update multiple rows based on value in one of the rows

Question

I have a process that is running daily on a datafeed and updating the last_update_time (a datetime field) on all items in our inventory database that are present in the datafeed. The problem I am having is when an item is removed from the datafeed its last_updated_time no longer gets updated in the database by my process. After an item is removed from the datafeed, I still want its last_update_time to be updated as long as there exists an item which shares the same item_group_id (nvarchar) with that item.

i.e. If there does exist an item in the databsase whose last_updated_time was updated by my process and that does share an item_group_id with other items that no longer exist in the datafeed, I want their last_update_time to be set to the most recent last_update_time of any item that shares that item_group_id.

For the purposes of this question we are talking about all columns in a single table (actual query is more complex but I've reduced down for clarity).

So, to begin with it looks like:

ItemID    GroupID     Last_Updated_time
----------------------------------------
1          345        5/26/2020 12:00pm
2          345        4/25/2020 12:00pm
3          234        4/25/2020 12:00pm

Afterwards

1          345        5/26/2020 12:00pm
2          345        5/26/2020 12:00pm
3          234        4/25/2020 12:00pm

I'd thought I could perhaps do an embedded query in my update statement as shown on one of the answers here How to UPDATE 1 row with multiple rows?, but I am struggling to figure out the syntax that ensures that I only update the last_updated_time field of items with the same GroupID.

Any help would be greatly appreciated. Using SQL Server Express in a C# application.

score 1 · Answer 1 · edited May 27 '20 at 04:25

1

It could be something like this:

update ProductsTable
set Last_Updated_time = T.Last_Updated_time
from ProductsTable
    Inner Join 
       (select GroupID,  Max(Last_Updated_time) As Last_Updated_time
         from DataFeed
         group by GroupID) T
   on ProductsTable.GroupID = T.GroupID

edited May 27 '20 at 04:25

Mukesh Arora

1,763
2
8
19

answered May 27 '20 at 04:23

Alexander

139
8

score 0 · Answer 2 · edited May 27 '20 at 05:18

0

You can achieve the same by using a CTE

;WITH CTE AS 
(
    SELECT GROUPID,  MAX(LAST_UPDATED_TIME) AS LAST_UPDATED_TIME
    FROM DATAFEED
    GROUP BY GROUPID
)
UPDATE Z
SET LAST_UPDATED_TIME = T.LAST_UPDATED_TIME
FROM PRODUCTSTABLE Z
INNER JOIN CTE T ON PRODUCTSTABLE.GROUPID = T.GROUPID

edited May 27 '20 at 05:18

Dale K

25,246
15
42
71

answered May 27 '20 at 04:29

Thiyagu

1,260
1
5
14

Pre-pending `with` with a semi-colon `;` is teaching bad habits... one should actually be terminating all statements with a `;`. – Dale K May 27 '20 at 05:19

score 0 · Answer 3 · answered May 27 '20 at 12:07

If you have large tables, you might find that apply gives the best performance:

update pt
    set Last_Updated_time = df.Last_Updated_time
    from ProductsTable pt cross apply
         (select max(Last_Updated_time) As Last_Updated_time
          from DataFeed df
          where pt.GroupID = df.GroupID
         ) df;

This can take advantage of an index on datafeed(groupID, Last_Updated_time).

SQL update multiple rows based on value in one of the rows

3 Answers3