求一个批量更改sql

[低代码开发平台]

--物料表
create table #tbMat(id int ,
Number varchar(10), Name varchar(10))
insert into #tbMat(id,Number,Name) values(1,'001','老干妈')
insert into #tbMat(id,Number,Name)values(2,'002','老干妈')
insert into #tbMat(id,Number,Name)values(3,'003','王老吉')


--销售表
create table #Sale(id int,MatID int,Many int)
insert into #Sale(id,MatID,Many) values(1,1,12)
insert into #Sale(id,MatID,Many)values(2,2,10)
insert into #Sale(id,MatID,Many)values(3,3,9)


--求一个sql语句，将销售表中的物料值更改为物料表中重复的第一个物料
update语句后，#Sale表的结果是：
id MatID Many
1 1 12
2 1 10
3 3 9

[-晴天]

--物料表
create table #tbMat(id int ,
  Number varchar(10), Name varchar(10))
insert into #tbMat(id,Number,Name) values(1,'001','老干妈')  
insert into #tbMat(id,Number,Name)values(2,'002','老干妈')
insert into #tbMat(id,Number,Name)values(3,'003','王老吉')


--销售表
create table #Sale(id int,MatID int,Many int)
insert into #Sale(id,MatID,Many) values(1,1,12)  
insert into #Sale(id,MatID,Many)values(2,2,10)
insert into #Sale(id,MatID,Many)values(3,3,9)

go
update a set matID=(select min(id) from #tbMat where name=(select name from #tbMat where id=a.MatID)) from #sale a
go
select * from #sale
/*
id          MatID       Many
----------- ----------- -----------
1           1           12
2           1           10
3           3           9

(3 行受影响)

*/

[Net攻城狮]

update #Sale
set #Sale.MatID=f.id
from #Sale,(select top 1 s.id from #tbMat as s,(select [Name],count([Name])as s from #tbMat
group by [Name]
having count([Name])>1) as f where s.name=f.name) as f
where #Sale.id='2'

[sql2015]

update a set a.MatId=C.id
from #Sale a join #tbMat b on a.MatID=b.id
join (select ROW_NUMBER() over(partition by name order by id) as rn,* from #tbMat) c
on b.Name=c.Name and c.rn=1