SQL: Add support for MERGE

CRDB supports UPSERT which is similar but different than MERGE. UPSERT is built off of INSERT while MERGE focuses on merging/synchronizing tables and provides: conditionality (where clauses) and DELETE support.

MERGE is part of the SQL standard and implemented by dbs like Oracle but not implemented in Postgres.

Edit: PostgreSQL is implementing this in PG 15: https://www.depesz.com/2022/03/31/waiting-for-postgresql-15-add-support-for-merge-sql-command/

CREATE TABLE Stock(item_id int UNIQUE, balance int);
INSERT INTO Stock VALUES (10, 2200);
INSERT INTO Stock VALUES (20, 1900);

CREATE TABLE Buy(item_id int, volume int);
INSERT INTO Buy values(10, 1000);
INSERT INTO Buy values(30, 300);

CREATE TABLE Sale(item_id int, volume int);
INSERT INTO Sale VALUES (10, 2200);
INSERT INTO Sale VALUES (20, 1000);

Now attempt to use MERGE:

root@127.0.0.1:58617/movr> MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id
 WHEN MATCHED THEN UPDATE SET balance = balance + Buy.volume
 WHEN NOT MATCHED THEN INSERT VALUES (Buy.item_id, Buy.volume);
invalid syntax: statement ignored: at or near "merge": syntax error
SQLSTATE: 42601
DETAIL: source SQL:
MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id
^

Jira issue: CRDB-4059

cockroachdb / cockroach

SQL: Add support for MERGE #51373