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 18c Express Edition Release 18.0.0.0.0 - Production | Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0 |
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production | 0 |
CREATE TABLE logs (
id NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL
, text VARCHAR2(20) UNIQUE
, q int DEFAULT 1
);
INSERT INTO logs (text) VALUES ('A');
1 rows affected
INSERT INTO logs (text) VALUES ('B');
1 rows affected
INSERT INTO logs (text) VALUES ('C');
1 rows affected
MERGE INTO logs USING (SELECT 'B' AS text FROM dual) cte ON (cte.text = logs.text)
WHEN MATCHED THEN UPDATE SET logs.q = logs.q + 1
WHEN NOT MATCHED THEN INSERT (logs.text)
VALUES (cte.text)
;
1 rows affected
SELECT * FROM logs;
| ID | TEXT | Q |
|---|---|---|
| 1 | A | 1 |
| 2 | B | 2 |
| 3 | C | 1 |
MERGE INTO logs USING (
SELECT text FROM logs WHERE text > 'A' UNION
SELECT 'Z' FROM dual
) cte ON (cte.text = logs.text)
WHEN MATCHED THEN UPDATE SET logs.q = logs.q + 1
WHEN NOT MATCHED THEN INSERT (logs.text)
VALUES (cte.text)
;
3 rows affected
SELECT * FROM logs;
| ID | TEXT | Q |
|---|---|---|
| 1 | A | 1 |
| 2 | B | 3 |
| 3 | C | 2 |
| 7 | Z | 1 |