--物料表
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 行受影响)

*/

------解决方案--------------------
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'

------解决方案--------------------
SQL code


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