With CTE, compare rows with same ID and updating oldest one to 0 SQL Server 2008

Asked for the logic yesterday, now I’ve worked a way around.

I have a big table used for reporting (170k~ rows) and many of them are “duplicated” but with updated dates (e.g. old record says John Doe worked from X to Y and new one says John Doe worked from X2 to Y2).

I want to know wich ones are the old and new so I setted a special column storing boolean values (default 1) so when I run the CTE I created if there is multiple records with the same ID change the old value to 0. With this cte I get the new record deleted and I’m trying to change the delete part to update the old record but I get an error 207 because the column valid isn’t valid.

Any help with my code it’s appreciated.

;with cte
    as (select Row_number() over (partition BY id_gen, perpro,valido order by ini desc) RN 
        from tstSolap)
delete from cte
--update cte set valido = 0
where RN > 1

Answer

A CTE somehow knows to identify the rows for the UPDATE or DELETE.

But to actually UPDATE a certain column, then you also need to include it in the CTE.
For a DELETE, that’s not a must.

And you could then also just include the other fields you use in that ROW_NUMBER.
That makes it easier to copy the CTE’s query, to test it manually before actually doing any changes.

Also, I doubt you actually need valido in the PARTITION BY to find duplicate (id_gen, perpro) tupples.

WITH CTE AS
(
  SELECT 
   id_gen, perpro, ini, valido,
   ROW_NUMBER() OVER (PARTITION BY id_gen, perpro ORDER BY ini DESC) RN 
  FROM tstSolap
)
UPDATE CTE
SET valido = 0
WHERE RN > 1
  AND (valido IS NULL OR valido <> 0)

Or if you want to correct both 0 or 1

WITH CTE AS
(
  SELECT 
   id_gen, perpro, ini, valido,
   IIF(ROW_NUMBER() OVER (PARTITION BY id_gen, perpro ORDER BY ini DESC) = 1, 1, 0) AS new_valido
  FROM tstSolap
)
UPDATE CTE
SET valido = new_valido
WHERE (valido != new_valido OR valido IS NULL)