單個更新重複行？

資源：

+-----------+-----------+-------------------+---------+---------+----------+----------+
| Productid | Imagetype |     Imagename     | Website | Thumb90 | Thumb200 | Thumb500 |
+-----------+-----------+-------------------+---------+---------+----------+----------+
|   3144000 | small     | 3144000_small.jpg | Google  |       1 |        0 |        0 |
|   3144005 | medium    | 3144005_medium.jpg| Google  |       1 |        0 |        0 |
|   3144005 | medium    | 3144005_medium.jpg| Google  |       0 |        1 |        0 |
|   3144005 | medium    | 3144005_medium.jpg| Google  |       0 |        0 |        1 |
+-----------+-----------+-------------------+---------+---------+----------+----------+

渡船：

+-----------+-----------+-------------------+---------+---------+----------+----------+
| Productid | Imagetype |     Imagename     | Website | Thumb90 | Thumb200 | Thumb500 |
+-----------+-----------+-------------------+---------+---------+----------+----------+
|   3144000 | small     | 3144000_small.jpg | Google  |       1 |        0 |        0 |
|   3144005 | medium    | 3144005_medium.jpg| Google  |       1 |        1 |        1 |
+-----------+-----------+-------------------+---------+---------+----------+----------+

這如何在單次更新中完成？

我的樣本：

UPDATE TARGET
SET ImageName  = SOURCE.ImageName,
   Website    = SOURCE.Website,
   Thumb90    = ISNULL(SOURCE.Thumb90,TARGET.Thumb90),
   Thumb200   = ISNULL(SOURCE.Thumb200,TARGET.Thumb200),
   Thumb500   = ISNULL(SOURCE.Thumb500,TARGET.Thumb500)        
FROM [luannw2016].[ImageThumbnailTrack] AS TARGET
INNER JOIN #ImageThumbnailTrack AS SOURCE
ON TARGET.ProductId = SOURCE.ProductId
AND TARGET.ImageType = SOURCE.ImageType

雖然 Merge 不是性能最佳的解決方案，但根據您的要求，它將處理您請求的內容（因為它會插入目標上不存在的行）。我將一個可能與您的不匹配的快速模式放在一起，但可以為您提供想法。

DECLARE @DestTable TABLE
(
 Productid BIGINT ,
 ImageType VARCHAR(10) ,
 ImageName VARCHAR(100) ,
 Website VARCHAR(20) ,
 Thumb90 TINYINT ,
 Thumb200 TINYINT ,
 Thumb500 TINYINT
);
DECLARE @SourceTable TABLE
   (
     Productid BIGINT ,
     ImageType VARCHAR(10) ,
     ImageName VARCHAR(100) ,
     Website VARCHAR(20) ,
     Thumb90 TINYINT ,
     Thumb200 TINYINT ,
     Thumb500 TINYINT
   );

INSERT  INTO @SourceTable
       ( Productid, ImageType, ImageName, Website, Thumb90, Thumb200,
         Thumb500 )
VALUES  ( 3144000, 'small', '3144000_small.jpg', 'Google', 1, 0, 0 )
,       ( 3144005, 'medium', '3144005_medium.jpg', 'Google', 1, 0, 0 )
,       ( 3144005, 'medium', '3144005_medium.jpg', 'Google', 0, 1, 0 )
,       ( 3144005, 'medium', '3144005_medium.jpg', 'Google', 0, 0, 1 );

;
WITH    cteConvergedProducts
         AS ( SELECT   Productid ,
                       ImageType ,
                       ImageName ,
                       Website ,
                       MAX(Thumb90) AS Thumb90 ,
                       MAX(Thumb200) AS Thumb200 ,
                       MAX(Thumb500) AS Thumb500
              FROM     @SourceTable
              GROUP BY Productid ,
                       ImageType ,
                       ImageName ,
                       Website
            )
   MERGE INTO @DestTable AS TARGET
   USING cteConvergedProducts AS SOURCE
   ON SOURCE.Productid = TARGET.Productid
       AND SOURCE.ImageType = TARGET.ImageType
   WHEN NOT MATCHED BY TARGET THEN
       INSERT ( Productid ,
                ImageType ,
                ImageName ,
                Website ,
                Thumb90 ,
                Thumb200 ,
                Thumb500
              )
       VALUES ( SOURCE.Productid ,
                SOURCE.ImageType ,
                SOURCE.ImageName ,
                SOURCE.Website ,
                SOURCE.Thumb90 ,
                SOURCE.Thumb200 ,
                SOURCE.Thumb500
              )
   WHEN MATCHED THEN
       UPDATE SET
              ImageName = SOURCE.ImageName ,
              Website = SOURCE.Website ,
              Thumb90 = SOURCE.Thumb90 ,
              Thumb200 = SOURCE.Thumb200 ,
              Thumb500 = SOURCE.Thumb500 ;

SELECT  *
FROM    @DestTable;

引用自：https://dba.stackexchange.com/questions/160066