Sqlserver Update same data from the same table

Question

I have a table that has registration of several CAR (repeated and not repeated), so I intend to update a field (active) of all repeated registrations leaving 1 more recent line.
Exemple of table data:
|ID| CAR| ACTIVE|
|1 |AAA-25-35 |0|
|2 |LDB-25-35 |0|
|3 |LDB-00-35 |0|
|4 |LDB-25-35 |0|
|5 |LDB-00-35 |0|
|6 |LDC-10-10 |0|
|7 |LDC-10-10 |0|
|8 |LDB-00-35 |0|
I want my data to be like this:
|ID| CAR| ACTIVE|
|1 |AAA-25-35 |0|
|2 |LDB-25-35 |1|
|3 |LDB-00-35 |1|
|4 |LDB-25-35 |0|
|5 |LDB-00-35 |1|
|6 |LDC-10-10 |1|
|7 |LDC-10-10 |0|
|8 |LDB-00-35 |0|

I tried to make this code, but it is not working correctly

UPDATE teste 
SET 
ACTIVE = 1
FROM
 (
 SELECT  COUNT (CAR ),  CAR, ID  FROM TESTE
        GROUP BY CAR, ID 
   HAVING  COUNT (CAR )> 1
  ORDER BY   COUNT (CAR ) DESC,  
 ID DESC   
     )  AS AA
    WHERE TESTE.ID = AA.ID

Answer 1

Hi @Joaquim Costa ,

If the ACTIVE column means the row is useful. I think the value of ACTIVE column for not repeated data need also to be 1. So, please try below T-SQL.

-----create table  
create table dbo.test( id int, CAR varchar(30), ACTIVE int)  
  
insert into dbo.test(id, CAR, ACTIVE)  
values   
(1, 'AAA-25-35', 0),  
(2, 'LDB-25-35', 0),  
(3, 'LDB-00-35', 0),  
(4, 'LDB-25-35', 0),  
(5, 'LDB-00-35', 0),  
(6, 'LDC-10-10', 0),  
(7, 'LDC-10-10', 0),  
(8, 'LDB-00-35', 0)  
  
 select * from dbo.test  
  
 ----update table  
 WITH CTE AS  
 (  
     SELECT   
         row_number() over (partition by CAR order by id) as t  
         , CAR , ACTIVE  
     FROM dbo.test  
 )  
 update CTE  
 SET ACTIVE = 1  
 WHERE t=1   
  
 select * from dbo.test  
  
 -----drop table  
drop table if exists dbo.test  

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I tested it and it didn't work 100% well, because of the car that is not repeated. I need that you do not change the line that is not repeated, simply the repeated ones, simply leaving a line.

Answer 3

the problem was solved by changing the code like this:

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)