A表:
PNO,UP,UP1,UP2,UP3,UP4,UP5,FLAG
p1 1,2,3,4,5,0
p2 2,3,4,2,3,1
p3 5,2,3,2,1,0B表:
SITE,PNO,CST
s1 p1,8
s2 p1,6现在要得到一个结果集:当A表的FLAG为0的时候判断B表 是否有 PNO相同的记录,并且SITE为s1。
有就更新UP,UP1,UP2,UP3,UP4,UP5 的值为 B表的CST的值。最终结果如下:C表:
PNO,UP,UP1,UP2,UP3,UP4,UP5
p1 8, 8, 8, 8, 8
p2 2,3,4,2,3
p3 5,2,3,2,1
from A,B where A.PNO=B.PNO and A.FLAG=0
FROM A A1 INNOR JOIN B ON A1.PNO=B.PNO WHERE FLAG=0 AND SITE='s1'
,A.PNO
,CASE WHEN A.FLAG=0 and Q.SITE='S1' THEN B.CST ELSE A.UP END as UP
,CASE WHEN A.FLAG=0 and Q.SITE='S1' THEN B.CST ELSE A.UP1 END as UP1
,CASE WHEN A.FLAG=0 and Q.SITE='S1' THEN B.CST ELSE A.UP2 END as UP2
,CASE WHEN A.FLAG=0 and Q.SITE='S1' THEN B.CST ELSE A.UP3 END as UP3
,CASE WHEN A.FLAG=0 and Q.SITE='S1' THEN B.CST ELSE A.UP4 END as UP4
,CASE WHEN A.FLAG=0 and Q.SITE='S1' THEN B.CST ELSE A.UP5 END as UP5
INTO C
from A left join (SELECT * FROM B WHERE [SITE]='S1') Q on A.PNO=Q.PNO