如何使用SQL中的Left Join更新数据

如何使用SQL中的Left Join更新数据



--档案
truncate table Archive
insert into Archive (id,gg,PaperTypeID)values(1,null,1)
insert into Archive (id,gg,PaperTypeID)values(2,null,2)
insert into Archive (id,gg,PaperTypeID)values(3,null,1)
insert into Archive (id,gg,PaperTypeID)values(4,null,1)
insert into Archive (id,gg,PaperTypeID)values(5,null,2)
insert into Archive (id,gg,PaperTypeID)values(6,null,1)
insert into Archive (id,gg,PaperTypeID)values(7,null,1)
insert into Archive (id,gg,PaperTypeID)values(8,null,1)
insert into Archive (id,gg,PaperTypeID)values(9,null,2)
insert into Archive (id,gg,PaperTypeID)values(10,null,2)
select * from Archive

--规格尺寸/纸张类型
truncate table dbo.SPaperType
insert into dbo.SPaperType values(1,'A4');
insert into dbo.SPaperType values(2,'16k');
insert into dbo.SPaperType values(5,'32k');
select * from dbo.SPaperType

--需求  
update Archive set gg='A4' where PaperTypeID=1
update Archive set gg='16k' where PaperTypeID=2
update Archive set gg='A3' where PaperTypeID not in(1,2)

--方法1
update Archive set Archive.gg=(select p.Caption from SPaperType p where Archive.PaperTypeID=p.id)
--方法2
update Archive set Archive.gg=p.Caption from Archive a left join SPaperType p on a.PaperTypeID=p.id
--方法3
merge into Archive a
 using SPaperType p
 on (a.PaperTypeID=p.id)
 when matched then update set a.gg=p.caption
 when not matched then insert (gg) values('A3'); --will be insert new data,but is not update data

http://blog.csdn.net/cadenzasolo