Trigger for update comparing the same row

Question

the thing is that I need Update a row when 2 or more rows have the same data, so I need to compare the same column for update.. This will explain me better:

I have this trigger (but I don't want to delete anything)

DELETE n1
FROM Codes_Cost n1, Codes_Cost n2
WHERE n1.Code = n2.Code and n1.PeriodID = n2.PeriodID
AND n1.ID < n2.ID;

I need to do something similar but Updating another row. Something like this

UPDATE Codes_Cost n2
SET n2.Status = 'Old'
WHERE n1.Code = n2.Code and n1.ID < n2.ID

But obviously it doesn't work. What can I do? What kind of query can help?

Answer 1

If I've understood correctly, this should work.

UPDATE Codes_Cost 
SET Status = 'Old'
FROM Codes_Cost 
INNER JOIN Codes_Cost n2 ON Codes_Cost.Code = n2.Code 
WHERE
Codes_Cost.ID < n2.ID

Edited for ambiguous table name.