SQL update latest entry

Update rows according to an effective date. 

1
2
3
4
5
6
7
8
9
UPDATE table 
SET activo = 0 
FROM table t1
INNER JOIN 
  (SELECT id,max(fechamodificacion) as LatestDate FROM table
   GROUP BY id) t2 
ON t1.id = t2.id and t1.fechamodificacion < t2.LatestDate
--for last row only use: t1.fechamodificacion = t2.LatestDate
--where