MERGE INTO EMPLOYEES_COPY EC
USING EMPLOYEES E ON (E.EMPLOYEE_ID = EC.EMPLOYEE_ID)

WHEN MATCHED THEN
    UPDATE SET EC.JOB_ID = E.JOB_ID WHERE EC.EMPLOYEE_ID <> 102
    DELETE WHERE EC.EMPLOYEE_ID = 102

When I run this code in Oracle, the row for Employee_id = 102 is not being deleted when I use that in Where condition for update clause, but if I don't include them in update clause, then the delete is happening - why?

Can you please let me know why this is the case?

I also see whenever I execute the above statement without Where on Update statement, I see 26 rows merged in the output, but when I execute with them I only see 25 rows merged. I'm attaching the screenshots below for reference.

Case #1 where the row with Employee_ID = 102 is not deleted:

WHERE IN UPDATE CLAUSE

ROW NOT DELETED

Case #2 where the row with Employee_ID = 102 is deleted

WHERE REMOVED

ROW DELETED

MERGE INTO EMPLOYEES_COPY EC
USING EMPLOYEES E ON (E.EMPLOYEE_ID = EC.EMPLOYEE_ID)

WHEN MATCHED THEN
    UPDATE SET EC.JOB_ID = E.JOB_ID WHERE EC.EMPLOYEE_ID <> 102
    DELETE WHERE EC.EMPLOYEE_ID = 102

When I run this code in Oracle, the row for Employee_id = 102 is not being deleted when I use that in Where condition for update clause, but if I don't include them in update clause, then the delete is happening - why?

Can you please let me know why this is the case?

I also see whenever I execute the above statement without Where on Update statement, I see 26 rows merged in the output, but when I execute with them I only see 25 rows merged. I'm attaching the screenshots below for reference.

Case #1 where the row with Employee_ID = 102 is not deleted:

WHERE IN UPDATE CLAUSE

ROW NOT DELETED

Case #2 where the row with Employee_ID = 102 is deleted

WHERE REMOVED

ROW DELETED

• sql
• oracle-database
• merge

• 1 Please do not post images of code or data, please only post text. Please show sample data and expected results. – Charlieface Commented Mar 3 at 11:44
• From the docs: "The only rows affected by this clause are those rows in the destination table that are updated by the merge operation. The DELETE WHERE condition evaluates the updated value, not the original value that was evaluated by the UPDATE SET ... WHERE condition. If a row of the destination table meets the DELETE condition but is not included in the join defined by the ON clause, then it is not deleted." so you need to remove the WHERE EC.EMPLOYEE_ID <> 102 condition. – Charlieface Commented Mar 3 at 11:48

1 Answer 1

From the MERGE documentation:

merge_update_clause

The merge_update_clause specifies the new column values of the target table or view. Oracle performs this update if the condition of the ON clause is true. If the update clause is executed, then all update triggers defined on the target table are activated.

Specify the where_clause if you want the database to execute the update operation only if the specified condition is true. The condition can refer to either the data source or the target table. If the condition is not true, then the database skips the update operation when merging the row into the table.

Specify the DELETE where_clause to clean up data in a table while populating or updating it. The only rows affected by this clause are those rows in the destination table that are updated by the merge operation. The DELETE WHERE condition evaluates the updated value, not the original value that was evaluated by the UPDATE SET ... WHERE condition. If a row of the destination table meets the DELETE condition but is not included in the join defined by the ON clause, then it is not deleted. Any delete triggers defined on the target table will be activated for each row deletion.

The important section is in the final paragraph:

The only rows affected by this clause are those rows in the destination table that are updated by the merge operation.

If the rows are not affected by the UPDATE operation (i.e. because UPDATE ... WHERE ... excludes them then they will also be excluded from the DELETE ... clause.