You do not specify what the primary key of the table is, so it is impossible to know if you are going to generate a unique key violation. Additionally, you do not clearly state what the business rule(s) are. Finally, from the looks of it, I want to state that this design pattern does not appear to be a best practice. I strongly advise you to provide some context around this as there may be a better way to either structure your data to avoid this or provide the result you are looking for without this update.
In any case, I did my best to answer your question:
DECLARE @T1 TABLE
(
ID CHAR(2) NOT NULL,
LinkID CHAR(2) NOT NULL,
Active BIT NOT NULL,
[Date] DATE NOT NULL
);
INSERT @T1
SELECT 'a1' , 'k1' , 0 , '2014-01-05' UNION ALL
SELECT 'g9' , 'k1' , 1 , '2014-02-13' UNION ALL
SELECT 'z5' , 'k2' , 0 , '2014-01-01' UNION ALL
SELECT 's6' , 'k2' , 0 , '2014-02-17' UNION ALL
SELECT 'd7' , 'k2' , 0 , '2014-03-21' UNION ALL
SELECT 'd8' , 'k2' , 0 , '2014-04-20' UNION ALL
SELECT 'd9' , 'k2' , 1 , '2014-05-02';
SELECT * FROM @T1;
UPDATE t
SET ID = t2.ID
FROM @T1 AS t
JOIN @T1 AS t2
ON t.LinkID = t2.LinkID
WHERE t.Active = 0
AND t2.Active = 1
AND t.ID <> t2.ID
SELECT * FROM @T1;
/*
ID LinkID Active Date
g9 k1 0 2014-01-05
g9 k1 1 2014-02-13
d9 k2 0 2014-01-01
d9 k2 0 2014-02-17
d9 k2 0 2014-03-21
d9 k2 0 2014-04-20
d9 k2 1 2014-05-02
*/
Update related felids
Posted - 2014-06-16 : 13:47:13
