SQLite Forum

Wishing for UPSERT syntax sugar
Login

Wishing for UPSERT syntax sugar

(1) By ddevienne on 2020-10-23 16:19:29 [source]

Hi. With INSERT or REPLACE, a many column insert statement names the columns once

insert into tab
( nk1,  nk2,  ... // Natural Key columns
, col1, col2, ... // "Normal" columns
, colA, colB, ... // More "Normal" columns
) values
( ?,    ?,    ...
, ?,    ?,    ...
, ?,    ?,    ...
)

But with UPSERT, which is superior IMO to the older INSERT or REPLACE, you have to name the columns 3 times:

insert into tab
( nk1,  nk2,  ...
, col1, col2, ...
, colA, colB, ...
) values
( ?,    ?,    ...
, ?,    ?,    ...
, ?,    ?,    ...
)
on conflict (nk1, nk2, ...)
do update set
  col1 = excluded.col1
, col2 = excluded.col2
, ...
, colA = excluded.colA
, colB = excluded.colB
, ...

Could there not be some kind of syntax sugar that automatically SETs all columns from the insert portion
which are not part of the on conflict list? Perhaps something like:

...
on conflict (nk1, nk2, ...)
do update set *

Possibly followed by more assignment-expressions, assuming for example a cnt (count) column defaulted to 1 on insert:

...
on conflict (nk1, nk2, ...)
do update set *, cnt = cnt + 1

And if cnt was not defaulted but part of the original insert, the cnt = would implicitly remove it form the set *,
similar to the columns in on conflict being removed (their values are unchanged, no need to rewrite them).

Just thinking aloud. I just found the syntax on the heavy side with many columns. WDYT?