Update lastest record for each key - SQL

Question

Hello All

Below is my Data

Key         Value       Date          STATUS
001         AAA          2010-01-01       E
001            BBB          2010-02-01       E
001            CCC          2010-03-01       E
002         XXX          2010-04-01       E
002            YYY          2010-05-01       E
002            ZZZ          2010-06-01       E
003         HHH          2010-03-01       E
003            GGG          2010-04-01       E 

I want to update the lastest record for given key with Status C (Current).

How can this be acheived with Update Statement.

Thanks

Answer 1

update table_name set status = 'C' where date = max(date);

Answer 2

http://odata.stackexchange.com/stackoverflow/q/8505/so3358976

-- SO3358976
-- update-lastest-record-for-each-key-sql

CREATE TABLE #temp ([Key] char(3) NOT NULL
, [Value] char(3) NOT NULL
, [Date] date NOT NULL
, [Status] char(1) NOT NULL) 

INSERT INTO #temp VALUES ('001', 'AAA', '2010-01-01', 'E')
,('001', 'BBB', '2010-02-01', 'E')
,('001', 'CCC', '2010-03-01', 'E')
,('002', 'XXX', '2010-04-01', 'E')
,('002', 'YYY', '2010-05-01', 'E')
,('002', 'ZZZ', '2010-06-01', 'E')
,('003', 'HHH', '2010-03-01', 'E')
,('003', 'GGG', '2010-04-01', 'E') 

;WITH Latest AS (
    SELECT [Key], MAX([Date]) AS LastDate FROM #temp GROUP BY [Key]
)
UPDATE t
SET [Status] = 'C'
FROM #temp AS t
INNER JOIN Latest
    ON t.[Key] = Latest.[Key]
    AND t.[Date] = Latest.[LastDate]​​​​​​​​​​

​SELECT * FROM #temp​

Answer 3

Hello all

I got this done as

WITH DISTKEYS (KEYS)
AS (SELECT DISTINCT [KEY] FROM dbo.myTable)
UPDATE TargetTable
SET [status] = 'C'
FROM dbo.myTable AS TargetTable JOIN DISTKEYS ds ON TargetTable.[key] = ds.[keys]
WHERE [date] = (SELECT MAX([date]) FROM dbo.myTable WHERE [key] = ds.[keys])

Thanks