删除重复行并更新引用

Question

如何删除表格中的重复行，并更新另一个表格中对应的行到剩下的行？重复仅限于姓名，ID列是自增列。

示例：

假设我们有两个表格Doubles和Data。

Doubles table (
   Id int,
   Name varchar(50)
)

Data Table (
    Id int,
    DoublesId int
)

现在我在Doubls表中有两个条目：

Id Name
1  Foo
2  Foo

同时在数据表中有两个条目：

ID DoublesId
1  1
2  2

最终，浮点数表中只应有一个条目：

Id Name
1  Foo

并且数据表中有两个条目：

Id DoublesId
1  1
2  1 

在“双打表”中，每个名称可以有任意数量的重复行（最多30行），同时也可以有常规的“单行”数据。

- Thomas

Answer 1

我还没有运行过这个代码，但希望它是正确的，并且足够接近最终解决方案以使您到达目的地。如果您愿意，请告诉我任何错误，我将更新答案。

--updates the data table to the min ids for each name
update Data
set id = final_id
from
  Data
join
  Doubles 
on Doubles.id = Data.id
join
(
  select 
    name
    min(id) as final_id
  from Doubles
  group by name
) min_ids
on min_ids.name = Doubles.name

--deletes redundant ids from the Doubles table
delete 
from Doubles
where id not in
(
  select 
    min(id) as final_id
  from Doubles
  group by name
)

- Robin

Answer 2

注意：我已经自行更改了您的ID名称，分别为DoubleID和DataID。我发现这样更容易处理。

DECLARE @Doubles TABLE (DoubleID INT, Name VARCHAR(50))
DECLARE @Data TABLE (DataID INT, DoubleID INT)

INSERT INTO @Doubles VALUES (1, 'Foo')
INSERT INTO @Doubles VALUES (2, 'Foo')
INSERT INTO @Doubles VALUES (3, 'Bar')
INSERT INTO @Doubles VALUES (4, 'Bar')

INSERT INTO @Data VALUES (1, 1)
INSERT INTO @Data VALUES (1, 2)
INSERT INTO @Data VALUES (1, 3)
INSERT INTO @Data VALUES (1, 4)

SELECT * FROM @Doubles
SELECT * FROM @Data

UPDATE @Data
SET DoubleID = MinDoubleID
FROM    @Data dt
        INNER JOIN @Doubles db ON db.DoubleID = dt.DoubleID
        INNER JOIN (
            SELECT    db.Name, MinDoubleID = MIN(db.DoubleID)
            FROM      @Doubles db
            GROUP BY  db.Name
        ) dbmin ON dbmin.Name = db.Name

/* Kudos to quassnoi */
;WITH q AS (
    SELECT Name, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Name) AS rn
    FROM @Doubles
    )
DELETE  
FROM    q
WHERE   rn > 1

SELECT * FROM @Doubles
SELECT * FROM @Data

- Lieven Keersmaekers

---

我自己也不太理解，但是这段示例代码似乎适用于SQL Server 2008。 - bambams

这帮助我实现了目标。 - Ali Shahzad

Answer 3

看看这个，我试过了，运行良好

--create table Doubles  (   Id int,   Name varchar(50))

--create table Data(    Id int,    DoublesId int)

--select * from doubles
--select * from data

Declare @NonDuplicateID int
Declare @NonDuplicateName varchar(max)
DECLARE @sqlQuery nvarchar(max)
DECLARE DeleteDuplicate CURSOR FOR

SELECT Max(id),name AS SingleID FROM Doubles
GROUP BY [NAME]

OPEN DeleteDuplicate 
FETCH NEXT FROM DeleteDuplicate INTO @NonDuplicateID, @NonDuplicateName
--Fetch next record
WHILE @@FETCH_STATUS = 0
BEGIN

--select b.ID , b.DoublesID, a.[name],a.id asdasd
--from doubles a    inner join  data b
--on
--a.ID=b.DoublesID
--where b.DoublesID<>@NonDuplicateID
--and a.[name]=@NonDuplicateName

print '---------------------------------------------';

select 
@sqlQuery = 
      'update b
        set b.DoublesID=' + cast(@NonDuplicateID as varchar(50)) + '
        from 
        doubles a
            inner join
        data b
            on
        a.ID=b.DoublesID
        where b.DoublesID<>' +  cast(@NonDuplicateID as varchar(50)) +
        ' and a.[name]=''' +  cast(@NonDuplicateName as varchar(max)) +'''';

print @sqlQuery
exec sp_executeSQL @sqlQuery
print '---------------------------------------------';

-- now move the cursor
FETCH NEXT FROM DeleteDuplicate INTO @NonDuplicateID ,@NonDuplicateName
END

CLOSE DeleteDuplicate --Close cursor
DEALLOCATE DeleteDuplicate --Deallocate cursor

---- Delete duplicate rows from original table
DELETE
FROM doubles
WHERE ID NOT IN
(
    SELECT MAX(ID)
    FROM doubles
    GROUP BY [NAME]
)

- Aamod

Answer 4

如果您正在使用MYSQL，以下方法适用于我。我分为两步完成：

• 第一步 -> 将所有数据行更新为一个双重表引用（具有最低ID）
• 第二步 -> 删除所有重复项并保留最低ID

第一步 ->

update Data 
join
  Doubles 
on Data.DoublesId = Doubles.id
join
(
  select name, min(id) as final_id
  from Doubles
  group by name
) min_ids
on min_ids.name = Doubles.name
set DoublesId = min_ids.final_id;

第二步 ->

DELETE c1 FROM Doubles c1
INNER JOIN Doubles c2 
WHERE
    c1.id > c2.id AND 
    c1.name = c2.name;

- Lahiru Gambheera