I have the following table that I need to update:
x
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.
Advertisement
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;