How do I update the two different tables using if and else condition in Oracle SQL server?

Question

Here is the Query which I need to convert into the Procedure.

MERGE INTO table1 SAI
 USING
 <IF> :SITE_PROJECTS_ID: != null &&:SITE_PROJECTS_ID: != 0 <THEN>(SELECT * from table2)AX
 ON (SAI.SITE_INFO_ID=AX.SITE_INFO_ID)
 WHEN MATCHED THEN UPDATE SET
 SAI.column1 = AX.column1,
 SAI.column2 = AX.column2,
 SAI.LAST_MODIFIED_BY = AX.LAST_MODIFIED_BY,
 SAI.LAST_MODIFIED_DATE = SYSDATE
 <ELSE>
 DUAL ON (SAI.SITE_INFO_ID=:SITE_INFO_ID:)
 WHEN MATCHED THEN UPDATE SET
 <IF> :value1: != null && :value2: != '' <THEN> SAI.SITE_TRAKER_SITE_ID = :value3:, <ENDIF>
  SAI.LAST_MODIFIED_DATE = sysdate
 <ENDIF>"

Above merge statement needs to get converted into the below PL/SQL format:

DECLARE v_rowcount NUMBER DEFAULT 0;
BEGIN 
FOR ax IN ( SELECT * FROM table2 ) 
LOOP 
UPDATE table1 SET status = ax.status 
WHERE project_id = ax.site_projects_id;
v_rowcount := SQL%rowcount; 
IF ( v_rowcount = 0 ) 
THEN INSERT INTO table1 ( fuze_project_id, status ) 
VALUES ( ax.site_projects_id, ax.status ); v_rowcount := 0; 
END IF; 
END LOOP;
END;/

I am confused for updating two tables as when the condition met I need a different table and on else I am getting DUAL table, and appropriately updation takes place.

Please guide me about how to proceed and accomplish this task.

Answer 1

Just use an IF-ELSE statement and split the MERGE statement into two:

BEGIN
  IF :SITE_PROJECTS_ID != 0 THEN
    MERGE INTO table1 SAI
    USING table2 AX
    ON (SAI.SITE_INFO_ID=AX.SITE_INFO_ID)
    WHEN MATCHED THEN
      UPDATE
      SET SAI.column1 = AX.column1,
          SAI.column2 = AX.column2,
          SAI.LAST_MODIFIED_BY = AX.LAST_MODIFIED_BY,
          SAI.LAST_MODIFIED_DATE = SYSDATE;
  ELSIF :value1 IS NOT NULL AND :value2 IS NOT NULL THEN
    UPDATE table1
    SET SITE_TRAKER_SITE_ID = :value3,
        LAST_MODIFIED_DATE  = sysdate;
  END IF;
END;
/

Note: != NULL will never be true; you want IS NOT NULL. Also, in Oracle, '' is identical to NULL.