批量更改sql

求一个批量更改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


[解决办法]

SQL code

--物料表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)goupdate a set matID=(select min(id) from #tbMat where name=(select name from #tbMat where id=a.MatID)) from #sale agoselect * from #sale/*id          MatID       Many----------- ----------- -----------1           1           122           1           103           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.idfrom #Sale a join #tbMat b on a.MatID=b.idjoin (select ROW_NUMBER() over(partition by name order by id) as rn,* from #tbMat) con b.Name=c.Name and c.rn=1