1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Converting merge statement to update and insert

Discussion in 'SQL PL/SQL' started by Kumar1122, Jun 1, 2017.

  1. Kumar1122

    Kumar1122 Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Bangalore
    I am trying to convert below merge statement in procedure into an update and insert statement:

    Code (SQL):
    BEGIN

            MERGE INTO sds_dia_error_per_min_data USING dual
            ON (sde_date = in_date AND sde_inst_name = in_inst_name
            AND sde_interface_name = in_interface_name AND sde_command_code = in_command_code AND sde_result_code = in_result_code)
            WHEN matched THEN
            UPDATE SET
            sde_error_sent = sde_error_sent + in_error_sent,
            sde_error_received = sde_error_received + in_error_received
            WHEN NOT MATCHED THEN
            INSERT (sde_date,sde_inst_name,sde_interface_name,sde_command_code,sde_result_code,sde_error_sent,
            sde_error_received)
            VALUES
            (in_date,in_inst_name,in_interface_name,
            in_command_code,in_result_code,in_error_sent,
            in_error_received);

            out_status := 0 ;

            COMMIT;

    Table definition :


    Code (SQL):
    CREATE TABLE "NTR70"."SDS_DIA_ERROR_PER_MIN_DATA"
       (    "SDE_DATE" TIMESTAMP (6) NOT NULL ENABLE,
        "SDE_INST_NAME" VARCHAR2(10 BYTE),
        "SDE_INTERFACE_NAME" VARCHAR2(50 BYTE),
        "SDE_COMMAND_CODE" VARCHAR2(50 BYTE),
        "SDE_RESULT_CODE" VARCHAR2(50 BYTE),
        "SDE_ERROR_SENT" NUMBER,
        "SDE_ERROR_RECEIVED" NUMBER,
         CONSTRAINT "PK_SDS_DIA_ERROR_PER_MIN" PRIMARY KEY ("SDE_DATE", "SDE_INST_NAME", "SDE_INTERFACE_NAME", "SDE_COMMAND_CODE", "SDE_RESULT_CODE");
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,622
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    My question is why? What is it you expect to gain from re-inventing the wheel by 'disassembling' this MERGE statement?
     
  3. Kumar1122

    Kumar1122 Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Bangalore
    Hi Zargon,

    Thanks for the reply.

    Because i am trying to migrate current Oracle DB to postgres enterprisedb version.

    In postgres enterprisedb version, it doesnt understand/ support merge statement.

    So before migrating, i need to convert this above merge statement into INSERT and UPDATE statement.

    Please need your help on this, since i am very new to SQL ....
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,622
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Then you should really should have used google.com to find this:

    http://www.postgresqltutorial.com/postgresql-upsert/

    so you could have written this:

    INSERT into sds_dia_error_per_min_data(sde_date,sde_inst_name,sde_interface_name,sde_command_code,sde_result_code,sde_error_sent,sde_error_received)
    VALUES (in_date,in_inst_name,in_interface_name,in_command_code,in_result_code,in_error_sent,in_error_received)
    on conflict(sde_date, sde_inst_name, sde_interface_name, sde_command_code, sde_result_code)
    do
    update
    SET
    sde_error_sent = sde_error_sent + in_error_sent,
    sde_error_received = sde_error_received + in_error_received;


    And you'll need to fix whatever variables that script is using to supply actual values.