代码之家 › 专栏 › 技术社区 › earthling

使用cte合并连接表中的值并删除重复项

common-table-expression unique-constraint tsql sql-server

0

earthling · 技术社区 · 16 年前

我有以下架构:

Parcels       Segments        SegmentsParcels
=========     ==========      =================
ParcelID      SegmentID       ParcelID
...           Name            SegmentID
              ...             id

数据的用户想要合并segments.names,并给了我一个当前segment.names映射到新segment.names的列表(所有当前都存在)。

现在我把这个列表放在一个临时表中,其中有currentID和newID要映射到。

我要做的是基于这个映射更新segmentsparcels中的segmentid。我可以用这句话:

update SegmentParcels set segmentID = [newID] from newsegments where segmentID = currentid

但这将创建一些副本,我对segmentparcels中的parcelid和segmentid有唯一的约束。

最好的办法是什么?我考虑过删除约束,然后再处理删除重复项(我曾经这样做过,可能还会这样做),但我希望有一个更简单的方法。

2 回复 | 直到 14 年前

1

0

KM. 16 年前

试试这样的:

CREATE TABLE #Results (oldID int, newid int)

update sp
    set segmentID = dt.newID
    OUTPUT sp.segmentID, dt.newID
        INTO #Results
    FROM SegmentParcels sp
    INNER JOIN (SELECT DISTINCT
                    segmentID, newID
                    FROM newsegments              ns 
                        INNER JOIN SegmentParcels sp ON ns.currentid = sp.segmentID
                        LEFT OUTER JOIN SegmentParcels sp2 ON ns.currentid = sp2.segmentID AND sp.ParcelID=sp2.ParcelID
                    WHERE sp2.ParcelID IS NULL

               ) dt ON dt.segmentID=sp.segmentID

我加入了output子句,它是sql server 2005及更高版本,因为如果您运行一个大型脚本,它可能会有帮助。这样你就有了新旧价值观。

2

0

earthling 16 年前

我最终放弃了约束,并使用公共表表达式删除了重复项。
下面是我使用的SQL:

-- drop unique constraint
ALTER TABLE [dbo].[SegmentParcels] DROP CONSTRAINT [uc_SegmentID_ParcelID]
GO

-- update segment ids to new values from map
update SegmentParcels
set segmentID = [newID] from newsegments where segmentID = currentid
GO

-- use common table expression to delete duplicates
WITH Duplicates(SegmentID, ParcelID, Id)
AS
(
    SELECT SegmentID, ParcelID, Min(Id) Id
    FROM SegmentParcels
    GROUP BY segmentID, parcelID
    HAVING Count(*) > 1
)
DELETE FROM SegmentParcels
WHERE Id IN (
    SELECT SegmentParcels.Id
    FROM SegmentParcels
    INNER JOIN Duplicates
    ON SegmentParcels.segmentID = Duplicates.SegmentID
    AND SegmentParcels.parcelID = Duplicates.ParcelID
    AND SegmentParcels.Id <> Duplicates.Id
) 

-- add unique constraint back
ALTER TABLE [dbo].[SegmentParcels] ADD  CONSTRAINT [uc_SegmentID_ParcelID] UNIQUE NONCLUSTERED 
(
    [segmentID] ASC,
    [parcelID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO