Help with update statement | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Help with update statement

Hi guys, I have the following sample data: PaperID StatusID StatusDate StatusKey 0001 4566 2003-09-03 00:00:00.000 D 0001 4222 2003-09-03 00:00:00.000 C 0001 4132 2003-09-01 00:00:00.000 A 0002 4222 1999-04-14 00:00:00.000 C 0002 4132 1999-04-10 00:00:00.000 A 0003 4132 1986-08-03 00:00:00.000 A 0003 4566 1986-07-29 00:00:00.000 D Now, if in the same paperID, there is a statusKy A and the status date is earlier than the other statusDate, i would like to change the other statusdate to be the same as the status date with the statusKy of ‘A’. if there is a statusKy ‘A’, but the other Statusky contains dates that are earlier than the date in StatusKy ‘A’, then leave it as what it was. The result i would want to see : PaperID StatusID StatusDate StatusKey 0001 4566 2003-09-01 00:00:00.000 D 0001 4222 2003-09-01 00:00:00.000 C 0001 4132 2003-09-01 00:00:00.000 A 0002 4222 1999-04-10 00:00:00.000 C 0002 4132 1999-04-10 00:00:00.000 A 0003 4132 1986-08-03 00:00:00.000 A 0003 4566 1986-07-29 00:00:00.000 D can you guys help me with this issue? i would appreciate it so much. thanks

try this update o
set StatusDate = a.StatusDate
from tbl o inner join tbl a
on o.PaperID = a.PaperID
and o.StatusKey <> ‘A’
and a.StatusKey = ‘A’
where o.StatusDate > a.StatusDate KH
]]>