sql - Update same data from the same table depending on a column

Question

I have a similar case of a table, Like the case of this link Update same data from the same table but in my case it must update depending on the column "dependency". In other words, it updates the repetitions in the tables always leaving the most recent line and for table that only have one line it does not update. My data is like this:

I want it to be updated like this:

I tryed this code:

create table dbo.test( id int, CAR varchar(30), ACTIVE int, dependency int)
    
 insert into dbo.test(id, CAR, ACTIVE, dependency)
 values 


(1, 'AAA-25-35', 0,1),
(2, 'LDB-25-35', 0,2),
(3, 'LDB-00-35', 0,2),
(4, 'LDB-25-35', 0,2),
(5, 'LDB-00-35', 0,2),
(6, 'LDC-10-10', 0,2),
(7, 'LDC-10-10', 0,2),
(8, 'LDB-00-35', 0,2),
(9, 'AAA-25-35', 0,1),
(10, 'AAA-25-35', 0,3),
(11, 'AAA-25-35', 0,3),
(12, 'BBB-25-35', 0,2),
(13, 'BBB-25-35', 0,3),
(14, 'BBB-25-35', 0,3)

GO 
SELECT * FROM TEST


WITH CTE AS
(
  SELECT ROW_NUMBER() OVER(PARTITION BY CAR ORDER BY ID) AS t,
         CAR,
         ACTIVE
  FROM Test
)

UPDATE CTE
SET ACTIVE = 1
WHERE t=1
AND EXISTS (SELECT 1 FROM CTE c WHERE c.CAR = CTE.CAR GROUP BY CAR HAVING COUNT(*) > 1)

go
SELECT * FROM  test

question from:https://stackoverflow.com/questions/65849074/update-same-data-from-the-same-table-depending-on-a-column

Answer 1

Try changing the SELECT and WHERE clauses:

WITH CTE AS (
  SELECT ROW_NUMBER() OVER(PARTITION BY CAR, dependency ORDER BY ID) AS t,
         LEAD(id) OVER (PARTITION BY CAR, dependency ORDER BY ID) as next_id,
         CAR,
         ACTIVE
  FROM Test
)
UPDATE CTE
SET ACTIVE = 1
WHERE t = 1 AND next_id IS NOT NULL