Merge문 변환 (Oracle To PostgreSQL)

:: ORACLE ::

MERGE INTO CC_TC_USER_M

USING DUAL ON ( USER_ID='USER_ID' )

WHEN MATCHED THEN

UPDATE SET USER_ID='USER_ID', USER_NM='USER_NM', USER_PWD='USER_PWD'

WHEN NOT MATCHED THEN

INSERT ( USER_ID,USER_NM,USER_PWD )

VALUES ( 'USER_ID','USER_NM','USER_PWD' )

    

    

    

:: POSTGRESQL ::

WITH UPSERT AS (

    SELECT 'USER_ID' AS USER_ID, 'USER_NM' AS USER_NM, 'USER_PWD' AS USER_PWD

)

,

UPDATE_OPTION AS (

    UPDATE CC_TC_USER_M AS UE

        SET USER_NM=UPSERT.USER_NM

    FROM UPSERT

    WHERE UE.USER_ID=UPSERT.USER_ID

)

INSERT INTO CC_TC_USER_M (USER_ID,USER_NM,USER_PWD)

SELECT UPSERT.USER_ID, UPSERT.USER_NM, UPSERT.USER_PWD FROM UPSERT

WHERE NOT EXISTS (

    SELECT USER_ID FROM CC_TC_USER_M AS UR WHERE UR.USER_ID = UPSERT.USER_ID

)