sql - 我怎么能 "Delete All"但仅当列具有特定值时？

Question

我需要将一组记录MERGE 到一个表中。源中的 UpdateType 列决定了我是否应该DELETE 目标中不在源中的行。

因此 UpdateType 将等于 D 或 R... D=Delta, R=Refresh

如果 D，不要DELETE 与目标不匹配
如果 R，请 DELETE 与目标不匹配。

我有一个遍历单个表的 WHILE，以便更好地模拟流程的工作方式。

我可以在 MERGE 中完成这个吗？或者我还有哪些其他选择？

SQL fiddle : http://www.sqlfiddle.com/#!3/9cdfe/16

这是我的示例，唯一的问题是......我不能在 WHEN NOT MATCHED BY SOURCE 子句中使用源值。

DECLARE @BaseTable TABLE
(   RN int  
    ,Store int
    ,UpdateType char(1)
    ,ItemNumber int
    ,Name varchar(50)
)

INSERT INTO @BaseTable
SELECT *
FROM
(
    SELECT 1 RN, 1 Store, 'D' UpdateType, 1 ItemNumber, 'Wheel' Name
    UNION ALL
    SELECT 2, 1, 'D', 1, 'Big Wheel'
    UNION ALL
    SELECT 3, 1, 'D', 2, 'Light'
    UNION ALL
    SELECT 4, 1, 'R', 1, 'Wide Wheel'
    UNION ALL
    SELECT 5, 1, 'D', 1, 'Small Wheel'
    UNION ALL
    SELECT 5, 1, 'D', 4, 'Trunk'
)B

SELECT bt.* FROM @BaseTable bt

DECLARE @Tab TABLE
(   Store int
    ,UpdateType char(1)
    ,ItemNumber int
    ,Name varchar(50)
)

DECLARE @count int = 1
--Loop over each row to mimic how the merge will be called.
WHILE @count <= 5
BEGIN  
    MERGE INTO @Tab T
    USING
    (
        SELECT bt.RN,
                bt.Store,
                bt.UpdateType,
                bt.ItemNumber,
                bt.Name,
                tab.Store IsRefresh
        FROM @BaseTable bt
        LEFT JOIN
        (   --If ANY previous ITERATION was a 'R' then, all subsequent UpdateType MUST = 'R'
            --I'm hoping there is a better way to accomplish this.
            SELECT Store
            FROM @Tab
            WHERE UpdateType = 'R'
            GROUP BY Store
            HAVING COUNT(Store) > 1
        )tab
            ON bt.Store = tab.Store
        WHERE bt.RN = @count
    )S
        ON S.Store = T.Store AND S.ItemNumber = T.ItemNumber
    WHEN MATCHED THEN
        UPDATE
        SET T.UpdateType = CASE WHEN S.IsRefresh IS NOT NULL THEN 'R' ELSE S.UpdateType END,
        T.Name = S.Name
    WHEN NOT MATCHED BY TARGET THEN
        INSERT(Store,UpdateType,ItemNumber,Name) VALUES(S.Store,S.UpdateType,S.ItemNumber,S.Name)
    --WHEN NOT MATCHED BY SOURCE AND S.UpdateType = 'R' THEN
    --  DELETE
        ;
    SET @count = @count + 1
END

SELECT * FROM @Tab

--@Tab Expected Result:
-- 1 'R' 1 'Small Wheel'
-- 1 'R' 4 'Trunk'

Answer 1

以下代码似乎可以执行您想要的操作:

CREATE TABLE #BaseTable
(   RN int  
    ,Store int
    ,UpdateType char(1)
    ,ItemNumber int
    ,Name varchar(50)
)

INSERT INTO #BaseTable
SELECT *
FROM
(
    SELECT 1 RN, 1 Store, 'D' UpdateType, 1 ItemNumber, 'Wheel' Name
    UNION ALL
    SELECT 2, 1, 'D', 1, 'Big Wheel'
    UNION ALL
    SELECT 3, 1, 'D', 2, 'Light'
    UNION ALL
    SELECT 4, 1, 'R', 1, 'Wide Wheel'
    UNION ALL
    SELECT 5, 1, 'D', 1, 'Small Wheel'
    UNION ALL
    SELECT 5, 1, 'D', 4, 'Trunk'
)B

CREATE TABLE #Tab
(   Store int
    ,UpdateType char(1)
    ,ItemNumber int
    ,Name varchar(50)
)
SELECT bt.* FROM #BaseTable bt  -- Output for debugging - delete in production. 

DECLARE @count int = 1
DECLARE @Input TABLE (Store int, UpdateType char(1), ItemNumber int, Name varchar(50))
--Loop over each row to mimick how the merge will be called.
WHILE @count <= 5
BEGIN
    DELETE FROM @Input
    INSERT INTO @Input SELECT Store, UpdateType, ItemNumber, Name  FROM #BaseTable WHERE RN = @Count

    SELECT * FROM @Input    -- Output for debugging - delete in production.

    -- Procedure Body
    DECLARE @Store int, @UpdateType char(1), @ItemNumber int, @Name varchar(50)
    DECLARE csrInput CURSOR FOR SELECT Store, UpdateType, ItemNumber, Name  FROM @Input
    OPEN csrInput
    WHILE 1=1
        BEGIN
            FETCH NEXT FROM csrInput INTO @Store, @UpdateType, @ItemNumber, @Name 
            IF @@FETCH_STATUS<>0 BREAK
            IF @UpdateType = 'D'
                MERGE INTO #Tab Dest
                USING (SELECT * FROM @Input WHERE Store = @Store AND ItemNumber = @ItemNumber) Src
                ON Dest.Store = Src.Store AND Dest.ItemNumber = Src.ItemNumber
                WHEN MATCHED THEN UPDATE SET Dest.UpdateType = Src.UpdateType, Dest.Name = Src.Name
                WHEN NOT MATCHED BY TARGET THEN INSERT (Store, UpdateType, ItemNumber, Name) VALUES (Src.Store, Src.UpdateType, Src.ItemNumber, Src.Name);
            ELSE    -- Assuming that @UpdateType can only be 'D' or 'R'...
                MERGE INTO #Tab Dest
                USING (SELECT * FROM @Input WHERE Store = @Store AND ItemNumber = @ItemNumber) Src
                ON Dest.Store = Src.Store AND Dest.ItemNumber = Src.ItemNumber
                WHEN MATCHED THEN UPDATE SET Dest.UpdateType = Src.UpdateType, Dest.Name = Src.Name
                WHEN NOT MATCHED BY TARGET THEN INSERT (Store, UpdateType, ItemNumber, Name) VALUES (Src.Store, Src.UpdateType, Src.ItemNumber, Src.Name)
                WHEN NOT MATCHED BY SOURCE THEN DELETE;

                SELECT * FROM #Tab  -- Output for debugging - delete in production. 

        END
    CLOSE csrInput
    DEALLOCATE csrInput
    -- End Procedure Body.

    SET @count += 1
END

最终输出:

Store UpdateType ItemNumber Name
1     D          1          Small Wheel
1     D          4          Trunk