Execute two UPDATEs on complementary data at once in Oracle

I have the following table that I need to update:

USER:

| ID | ACTIVE | REF_COL | COL_2 | COL_3 |
|----|--------|---------|-------|-------|
| 1  |    1   |  value  | value | value |
| 2  |    0   |  value  | value | value |
| 3  |    1   |  value  | value | value |

I execute the following two UPDATE statements separately:

Statement-1

UPDATE
    USER
SET
    ACTIVE = 1
WHERE
    REF_COL IN (
        -- Subquery that generates a list of values
    )

Statement-2

UPDATE
    USER
SET
    ACTIVE = 0
WHERE
    REF_COL NOT IN (
        -- Subquery that generates a list of values
    )

The Subquery that generates a list of values is the same for both of the UPDATE queries.

Is there any way which will help me to execute the query at once, like MERGE?

The following query using MERGE statement is invalid:

MERGE INTO USER U
USING (
    -- Subquery that generates a list of values
) T
ON (U.REF_COL = T.VALUE)
WHEN MATCHED THEN
    UPDATE SET U.ACTIVE = 1
WHEN NOT MATCHED THEN
    UPDATE SET U.ACTIVE = 0

Since the WHEN NOT MATCHED THEN clause expects to have an INSERT statement.

Answer

Here is a merge version with example. You have to place logic in source part:

merge into users tgt
using (
  select u.ref_col, nvl2(s.ref_col, 1, 0) active
    from users u 
    left join subquery s on u.ref_col = s.ref_col ) src
on (tgt.ref_col = src.ref_col)
when matched then update set active = src.active;

^{dbfiddle example}

Advertisement

Answer