CODEWITHSUNDEEP
sqloracle-databaseplsql
radha
radha

Reputation: 175

How to use Update command with CASE statement in Oracle?

I have to update the Matching Flag Column depending on the following Condition :

When F_ZN=IC_ZN AND F_STN=IC_STN then Matching Flag would be set to 1 else 0 ,I could think of this but I don't know the exact syntax .

UPDATE F_STATE_MAPPING SET MATCHING_FLAG=CASE WHEN F_ZN=IC_ZN AND F_STN=IC_STN THEN 1 ELSE 0 END  
FROM 
(
SELECT F_ZN,F_STN,IC_ZN,IC_STN FROM 
(
SELECT A.ZN_CD AS F_ZN,A.STN AS F_STN,B.ZN_CD AS IC_ZN,B.STN AS IC_STN FROM 
F_STATE_MAPPING A,TEMP_STN_STATE_MAPPING B WHERE A.ZN_CD=B.ZN_CD AND A.STN=B.STN
)
)

Schema of F_STATE_MAPPING Table is 

    ZN_CD VARCHAR2(4)
    STN   VARCHAR2(4) 
MATCHING_FLAG NUMBER(1)

Please guide .

Upvotes: 0

Views: 811

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You about using exists?

UPDATE F_STATE_MAPPING
    SET MATCHING_FLAG = (CASE WHEN EXISTS (SELECT 1
                                           FROM TEMP_STN_STATE_MAPPING
                                           WHERE F_ZN = IC_ZN AND F_STN = IC_STN
                                          )
                              THEN 1 ELSE 0
                         END) ;

Upvotes: 2

Gabriel Durac
Gabriel Durac

Reputation: 2760

If you want to update a column for a table it must first exist:

ALTER TABLE F_STATE_MAPPING ADD  MATCHING_FLAG int

Then you can update it 

UPDATE 
(
    SELECT A.MATCHING_FLAG,
            CASE WHEN (A.ZN_CD=B.ZN_CD AND A.STN=B.STN) THEN 1 ELSE 0 END AS NEWVALUE
    FROM F_STATE_MAPPING A 
    LEFT JOIN TEMP_STN_STATE_MAPPING B ON A.ZN_CD=B.ZN_CD AND A.STN=B.STN
) t
SET t.MATCHING_FLAG = t.NEWVALUE

It can also be written as:

 UPDATE 
    (
        SELECT A.MATCHING_FLAG,
                CASE WHEN (B.ZN_CD is null) THEN 0 ELSE 1 END AS NEWVALUE
        FROM F_STATE_MAPPING A 
        LEFT JOIN TEMP_STN_STATE_MAPPING B ON A.ZN_CD=B.ZN_CD AND A.STN=B.STN
    ) t
    SET t.MATCHING_FLAG = t.NEWVALUE

Upvotes: 1

Related Questions