Update from Max Value of ID in Another Table

Question

I need some help with the best way to achieve the following:

Source Table:

tbl_ID  name    Row_id  change_time
1       test1   1             2016-01-01 09:00:00
1       test2   2             2016-01-01 10:00:00
50      test3   3             2016-02-01 09:00:00
50      test4   4             2016-02-01 11:00:00
66      test50  5             2016-03-01 11:00:00

Destination Table:

tbl_ID  name    Row_id   RecordStatus
1       test1   1         Expired
1       test2   2         Expired ----need to update this to  'New Record'
50      test3   3         Expired
50      test4   4         Expired ---- need to update this to  'New Record'
66      test50  5         Expired ---- need to update this to  'New Record'        

I need to update the destination table record status column with 'New record' by joining to the source table by:

• tbl_ID
• Row_id columns.

WHERE the 'change_time' is MAX for each ID.

Any suggestions on the code I would be grateful.

Answer 1

This should do it:

SAMPLE DATA:

CREATE TABLE #temp(tbl_ID      INT,
                name        VARCHAR(50),
                Row_id      INT,
                change_time DATETIME);

INSERT INTO #temp
VALUES
      (1, 'test1', 1, '2016-01-01 09:00:00'),
      (1, 'test2', 2, '2016-01-01 10:00:00'),
      (50, 'test3', 3, '2016-02-01 09:00:00'),
      (50, 'test4', 4, '2016-02-01 11:00:00'),
      (66, 'test50', 5, '2016-03-01 11:00:00');

CREATE TABLE #temp2(tbl_ID       INT,
                name         VARCHAR(50),
                Row_id       INT,
                RecordStatus VARCHAR(50));

INSERT INTO #temp2
VALUES
      (1, 'test1', 1, 'Expired'),
      (1, 'test2', 2, 'Expired'), ----need to update this to  'New Record'
      (50, 'test3', 3, 'Expired'),
      (50, 'test4', 4, 'Expired'), ---- need to update this to  'New Record'
      (66, 'test50', 5, 'Expired');  ---- need to update this to  'New Record' 

QUERY:

;WITH A
    AS (SELECT *,
             RANK() OVER(PARTITION BY tbl_ID ORDER BY change_time) AS Ranking
        FROM   #temp),
    B
    AS (SELECT A.*
        FROM   A
             INNER JOIN
                     (SELECT tbl_ID,
                            MAX(Ranking) Ranking
                      FROM   A
                      GROUP BY tbl_ID) AS B ON A.tbl_ID = B.tbl_ID
                                          AND A.Ranking = B.Ranking)
    UPDATE A
      SET  A.RecordStatus = 'New Record'
    FROM #temp2 AS A
        INNER JOIN B B ON A.tbl_ID = B.tbl_ID
                       AND A.Row_id = B.Row_id;

RESULTS:

SELECT *
FROM   #temp2;