SQL Server 2017

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

CREATE TABLE prod1	
	([ID] varchar(7), [name] varchar(20),[SN] varchar(20));
INSERT INTO prod1
	([ID], [name],[SN])
VALUES
('a01','abc','P0123'),
('a02','dfe','D1254'),
('a03','dff','P2256'),
('a04','ggg','D2223'),
('a05','hhh','P1125'),
('a06','jjj','D5563');
6 rows affected

select *
from prod1


ID
name
SN


a01
abc
P0123

a02
dfe
D1254

a03
dff
P2256

a04
ggg
D2223

a05
hhh
P1125

a06
jjj
D5563


CREATE TABLE prod2
	([ID] varchar(7), [name] varchar(20),[SN] varchar(20));
INSERT INTO prod2
	([ID], [name],[SN])
VALUES
('a01','abc','GA01'),
('a02','dfe','DA02'),
('a03','dff','PA56'),
('a04','ggg','DP22'),
('a05','hhh','PP12'),
('a06','jjj','DD56'),
('a07','xxx','yy88');
7 rows affected

select *
from prod2


ID
name
SN


a01
abc
GA01

a02
dfe
DA02

a03
dff
PA56

a04
ggg
DP22

a05
hhh
PP12

a06
jjj
DD56

a07
xxx
yy88


update A
set A.SN=B.SN
from prod2 as A
inner join prod1 as B on B.ID=A.ID 
6 rows affected

select *
from prod2


ID
name
SN


a01
abc
P0123

a02
dfe
D1254

a03
dff
P2256

a04
ggg
D2223

a05
hhh
P1125

a06
jjj
D5563

a07
xxx
yy88

ID	name	SN
a01	abc	P0123
a02	dfe	D1254
a03	dff	P2256
a04	ggg	D2223
a05	hhh	P1125
a06	jjj	D5563

ID	name	SN
a01	abc	GA01
a02	dfe	DA02
a03	dff	PA56
a04	ggg	DP22
a05	hhh	PP12
a06	jjj	DD56
a07	xxx	yy88