clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1811948 fiddles created (25315 in the last week).

CREATE TABLE [商品資料] ( [商品條碼] Nvarchar(40) NULL , [會員價] INT NULL , [牌價] INT NULL); INSERT [商品資料] ([商品條碼], [會員價], [牌價]) VALUES ('123',1000,1010), ('456',2000,2010); SELECT * FROM [商品資料];
商品條碼 會員價 牌價
123 1000 1010
456 2000 2010
 hidden batch(es)


CREATE TABLE [價格資料] ( [商品條碼] Nvarchar(40) NULL , [商品價格] INT NULL , [會員價格] INT NULL , [牌價] INT NULL , [修改日期] Nvarchar(40) NULL); INSERT [價格資料] ([商品條碼],[商品價格],[會員價格],[牌價],[修改日期]) VALUES ('123',1100,950,1010,'20200101'), ('123',3100,2950,3010,'20200201'), ('456',5100,4950,5010,'20200301'), ('456',7100,6950,7010,'20200401'); SELECT * FROM [價格資料];
商品條碼 商品價格 會員價格 牌價 修改日期
123 1100 950 1010 20200101
123 3100 2950 3010 20200201
456 5100 4950 5010 20200301
456 7100 6950 7010 20200401
 hidden batch(es)


UPDATE A SET A.[會員價]=C.[會員價格], A.[牌價]=C.牌價 FROM [商品資料] AS A INNER JOIN [價格資料] AS C ON A.[商品條碼]=C.[商品條碼] INNER JOIN ( SELECT B.[商品條碼],MAX(B.[修改日期]) AS [修改日期] FROM [價格資料] AS B GROUP BY B.[商品條碼] ) AS D ON C.[商品條碼]=D.[商品條碼] AND C.[修改日期]=D.[修改日期];
2 rows affected
 hidden batch(es)


SELECT * FROM [商品資料];
商品條碼 會員價 牌價
123 2950 3010
456 6950 7010
 hidden batch(es)