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);

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


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

SELECT *
FROM [商品資料];


商品條碼
會員價
牌價


123
2950
3010

456
6950
7010

商品條碼	商品價格	會員價格	牌價	修改日期
123	1100	950	1010	20200101
123	3100	2950	3010	20200201
456	5100	4950	5010	20200301
456	7100	6950	7010	20200401