Oracle 18c

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