Support `MERGE` statements from the ANSI Standard

[joshuamzm]

Reference: https://en.wikipedia.org/wiki/Merge_%28SQL%29

Conversation: https://clojurians.slack.com/archives/C66EM8D5H/p1691702334368519

[seancorfield]

:merge-into would be a strange hybrid that would take [:table {:using ...} case-clauses] as arguments, and we'd need [:matched] as special syntax to make this work I think... or perhaps overlay :case / :else logic? I need to think about this some more.

[seancorfield]

It can't overlay :case / :else even tho' it has (on the surface) a similar structure: the update / insert / delete clauses it supports are only partial statements (because merge into implies the table).

The optional case predicates make this even harder, or at least more irregular as a DSL.

The :case logic could be mapped onto this, sort of, since matched clauses are like case clauses except that the condition is optional and, if present, merges onto when matched and (otherwise just when matched) followed by a DSL map assumed for :set or {:delete nil} (not many options for that) and the :else condition could map onto when not matched but would have an optional condition (again and <predicate>) followed by a DSL map assumed for :columns (optional) and :values.

{:merge-into [:table [:source [:= :table.id :source.id]] :foo {:set {:a 1}} {:set {:b 1}} :else {:values [{:a 2 :b 2}]}]}
=>
MERGE INTO table USING source ON table.id = source.id
  WHEN MATCHED AND foo THEN UPDATE SET a = 1
  WHEN MATCHED THEN UPDATE SET b = 1
  WHEN NOT MATCHED THEN INSERT (a, b) VALUES (1, 1)

Something like that...

[seancorfield]

See thread https://clojurians.slack.com/archives/C66EM8D5H/p1744647807488419

[falberto]

Oracle supports the MERGE INTO operation, which works like an upsert.
The documentation is provided here for reference only.

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/MERGE.html