日期:2014-05-18  浏览次数:20440 次

联表更新问题
有以下两表(部分数据):
表PURTH
TH001 TH002 TH054
3401 2011060038 200.000000
3401 2011060039 5000.000000
3401 2011060044 3000.000000
3401 2011060045 50.000000
3401 2011070001 1.000000
3401 2011060026 10000.000000
3498 2011060004 56.100000
3498 2011060005 10150.000000
表ACPTB
TB005 TB006
3498 2011100014 
3498 2012010002 
3498 2011120007 
3498 2012020001 
3498 2012020001 
3498 2011100015 
3498 2011110010 
3498 2011120006 
现要更新表PURTH中字段TH054为0,条件是TH001和TH002联合时,不在表ACPTB中的TB005和TB006的联合,因为TH001和TH002组合在一起才是唯一,分开了就都不是唯一值


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

--我改了一条数据
declare @PURTH table (TH001 int,TH002 int,TH054 numeric(11,6))
insert into @PURTH
select 3401,2011060038,200.000000 union all
select 3401,2011060039,5000.000000 union all
select 3401,2011060044,3000.000000 union all
select 3401,2011060045,50.000000 union all
select 3401,2011070001,1.000000 union all
select 3401,2011060026,10000.000000 union all
select 3498,2011060004,56.100000 union all
select 3498,2011060005,10150.000000

declare @ACPTB table (TB005 int,TB006 int)
insert into @ACPTB
select 3401,2011060038 union all
select 3498,2012010002 union all
select 3498,2011120007 union all
select 3498,2012020001 union all
select 3498,2012020001 union all
select 3498,2011100015 union all
select 3498,2011110010 union all
select 3498,2011120006

update @PURTH set TH054=0 from @PURTH a left join @ACPTB b
on a.TH001=b.TB005 and a.TH002=b.TB006 where b.TB005 is null

select * from @PURTH
/*
TH001       TH002       TH054
----------- ----------- ---------------------------------------
3401        2011060038  200.000000
3401        2011060039  0.000000
3401        2011060044  0.000000
3401        2011060045  0.000000
3401        2011070001  0.000000
3401        2011060026  0.000000
3498        2011060004  0.000000
3498        2011060005  0.000000
*/