By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;
| BANNER | BANNER_FULL | BANNER_LEGACY | CON_ID |
|---|---|---|---|
| Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production | Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 |
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production | 0 |
CREATE TABLE t1 (
COL1 VARCHAR2(1),
COL2 VARCHAR2(1),
COL3 NUMBER
);
INSERT INTO t1
SELECT t.*, NULL from (
SELECT 'A', 'X' from dual UNION ALL
SELECT 'A', 'Y' from dual UNION ALL
SELECT 'A', 'Z' from dual UNION ALL
SELECT 'B', 'X' from dual UNION ALL
SELECT 'B', 'Y' from dual UNION ALL
SELECT 'B', 'Z' from dual
) t;
6 rows affected
COMMIT;
CREATE TABLE t2 (
COL2 VARCHAR2(1),
VALUE NUMBER
);
INSERT INTO t2
SELECT * from (
SELECT 'X', 10 from dual UNION ALL
SELECT 'Y', 100 from dual
);
2 rows affected
COMMIT;
MERGE INTO t1
USING t2
ON (t2.col2=t1.col2)
WHEN MATCHED THEN UPDATE SET
t1.col3 = CASE WHEN t1.col1='A'THEN t2.value
WHEN t1.col1='B'THEN 200 END;
4 rows affected
select * from t1
| COL1 | COL2 | COL3 |
|---|---|---|
| A | X | 10 |
| A | Y | 100 |
| A | Z | null |
| B | X | 200 |
| B | Y | 200 |
| B | Z | null |