sql - 根据列更新同一表中的相同数据

Question

我有一个类似的表情况，就像这个链接的情况一样，从同一个表中更新相同的数据， 但在我的情况下，它必须根据“依赖项”列进行更新。换句话说，它更新表中的重复项，总是留下最新的一行，而对于只有一行的表，它不会更新。我的数据是这样的：

我希望它像这样更新：

我试过这段代码：

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

标签： sqlsql-server

Answer 1

尝试更改SELECTandWHERE子句：

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