SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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


CREATE TABLE [價格資料] (
[商品條碼] Nvarchar(40) NULL ,
[商品價格] INT NULL ,
[會員價格] INT NULL ,
[牌價] INT NULL ,
[修改日期] Nvarchar(40) NULL,
[修改序號] INT NULL);

INSERT [價格資料] ([商品條碼],[商品價格],[會員價格],[牌價],[修改日期],[修改序號])
VALUES 
('123',1200,950,1010,'20200101',1),
('123',1100,900,1020,'20200101',2),
('123',3200,2950,3010,'20200201',1),
('123',3100,2900,3020,'20200201',2),
('456',5200,4950,5010,'20200301',1),
('456',5100,4900,5020,'20200301',2),
('456',7100,6950,7010,'20200401',1),
('456',7200,6900,7020,'20200401',2);

SELECT *
FROM [價格資料];


商品條碼
商品價格
會員價格
牌價
修改日期
修改序號


123
1200
950
1010
20200101
1

123
1100
900
1020
20200101
2

123
3200
2950
3010
20200201
1

123
3100
2900
3020
20200201
2

456
5200
4950
5010
20200301
1

456
5100
4900
5020
20200301
2

456
7100
6950
7010
20200401
1

456
7200
6900
7020
20200401
2


UPDATE A
SET A.[會員價]=C.[會員價格],
A.[牌價]=C.牌價
FROM [商品資料] AS A
INNER JOIN [價格資料] AS C ON A.[商品條碼]=C.[商品條碼]
INNER JOIN (
SELECT X.[商品條碼],X.[修改日期],X.[商品價格]
FROM (SELECT B.[商品條碼],B.[修改日期],B.[商品價格],
Row_Number() OVER (PARTITION BY B.[商品條碼] ORDER BY B.[修改日期] DESC,B.[商品價格] DESC) AS ROWNUM 
FROM [價格資料] AS B) AS X
WHERE X.[ROWNUM]=1
) AS D ON C.[商品條碼]=D.[商品條碼] AND C.[修改日期]=D.[修改日期]
AND C.[商品價格]=D.[商品價格]
2 rows affected

SELECT *
FROM [商品資料];


商品條碼
會員價
牌價


123
2950
3010

456
6900
7020

商品條碼	商品價格	會員價格	牌價	修改日期	修改序號
123	1200	950	1010	20200101	1
123	1100	900	1020	20200101	2
123	3200	2950	3010	20200201	1
123	3100	2900	3020	20200201	2
456	5200	4950	5010	20200301	1
456	5100	4900	5020	20200301	2
456	7100	6950	7010	20200401	1
456	7200	6900	7020	20200401	2