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