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

id'7238 · Accepted Answer · 2021-01-22 17:30:11Z

2

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

answered Jan 22, 2021 at 17:30

id'7238

2,7261 gold badge7 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Joaquim Costa Over a year ago

Illustrious, an issue that was not verified, he is not updating the column "ACTIVE" to the column "CAR" which is reflected in the same "DEPENDENCY". You can check the CAR = 'LDB-00-35', you should update two first lines and leave one. The code did the reverse.

id'7238 Over a year ago

Look at this fiddle. The result is exactly the same as the output described in the question.

Joaquim Costa Over a year ago

I was able to solve it, I removed the update condition "WHERE t = 1 AND next_id IS NOT NULL" and left only "WHERE next_id IS NOT NULL" I was wrong to share the first result. Thank you for your attention.

Collectives™ on Stack Overflow

Update same data from the same table depending on a column

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related