case else statement confusion - HELP

[christer99]

How do I set the REWR column below with a "case else" statement as opposed to running two different SQL update statements just to set something either 'Y' or 'N' ??


UPDATE POLICY
SET REWR='Y'
WHERE (EXISTS (SELECT 'x'
FROM POLICY B INNER JOIN
POLICY POLICY_1 ON LEFT(B.POL_IDX, 7) = LEFT(POLICY_1.POL_IDX, 7) AND B.EXP = POLICY_1.EFF AND B.TYPE = POLICY_1.TYPE AND
B.POL_IDX <> POLICY_1.POL_IDX
WHERE POLICY_1.POL_IDX=POLICY.POL_IDX ))



UPDATE POLICY
SET REWR='N'
WHERE (NOT EXISTS (SELECT 'x'
FROM POLICY B INNER JOIN
POLICY POLICY_1 ON LEFT(B.POL_IDX, 7) = LEFT(POLICY_1.POL_IDX, 7) AND B.EXP = POLICY_1.EFF AND B.TYPE = POLICY_1.TYPE AND
B.POL_IDX <> POLICY_1.POL_IDX
WHERE POLICY_1.POL_IDX=POLICY.POL_IDX ))

 

[AlexCuse]

I believe it would be something like this (not tested).

UPDATE POLICY
SET REWR= CASE WHEN  
       (EXISTS (SELECT     'x'
        FROM         POLICY B INNER JOIN POLICY POLICY_1
        ON LEFT(B.POL_IDX, 7) = LEFT(POLICY_1.POL_IDX, 7)
        AND B.EXP = POLICY_1.EFF AND B.TYPE =POLICY_1.TYPE 
        AND B.POL_IDX <> POLICY_1.POL_IDX
        WHERE  POLICY_1.POL_IDX=POLICY.POL_IDX  )) 
THEN 'Y' ELSE 'N' END

Make sure to test it as a select first, I am only about 95% on the phrasing of the EXISTS.

HTH,

Alex



It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.