SQL之EXISTS用法示例
--1.更新表信息 --嵌套EXISTS最内层大范围筛选修改日期的"FID"信息 --次内层进一步筛选符合调拨单的"FID"信息 --嵌套外层筛选符合"FID"条件的分录内码"FENTRYID"信息 UPDATE A set a.FAMOUNT_LC=0,a.FPRICE=0 FROM T_SAL_OUTSTOCKENTRY_C A WHERE EXISTS(SELECT FENTRYID FROM T_SAL_OUTSTOCKENTRY B WHERE A.FENTRYID=B.FENTRYID AND EXISTS(SELECT FID FROM T_SAL_OUTSTOCKENTRY_R C WHERE C.FID=B.FID AND C.FSRCBILLNO LIKE 'CA%' AND EXISTS(SELECT FID FROM T_SAL_OUTSTOCK D WHERE D.FID=C.FID AND CONVERT(VARCHAR(10),D.FDATE,120)>='2018-07-01' ) ) )
--2.更新表信息 --方法一: UPDATE B set B.FCOSTAMOUNT=0,B.FCOSTPRICE=0 FROM T_SAL_OUTSTOCK A LEFT JOIN T_SAL_OUTSTOCKENTRY_F B ON B.FID=A.FID WHERE CONVERT(VARCHAR(10),A.FDATE,120)>='2018-07-01' --A.FBILLNO='JC1807-0322' AND ISNULL(B.FCOSTAMOUNT,0)<>0 AND EXISTS(SELECT FID FROM T_SAL_OUTSTOCKENTRY_R C WHERE A.FID=C.FID AND C.FSRCBILLNO LIKE 'CA%') --方法二: UPDATE A SET A.FCOSTAMOUNT=0,A.FCOSTPRICE=0 FROM T_SAL_OUTSTOCKENTRY_F A WHERE EXISTS(SELECT B.FID FROM T_SAL_OUTSTOCKENTRY_R B WHERE A.FID=B.FID AND B.FSRCBILLNO LIKE 'CA%' AND EXISTS(SELECT C.FID FROM T_SAL_OUTSTOCK C WHERE C.FID=B.FID AND CONVERT(VARCHAR(10),C.FDATE,120)>='2018-07-01' ) ) AND ISNULL(A.FCOSTAMOUNT,0)<>0