SQLite: Is there a way for multiline upsert?

user_7487158 0
Viewed 38

I am searchng for a multiline like upsert. Lets assume that I have the following SQLite schema only containing an ID and many integers:

    CREATE TABLE table (ID INT PRIMARY KEY, A INT, ..., Z INT);

If I want to insert some integers A, B and C and leave the rest like it is I can use Upsert like:

    INSERT INTO table (ID, A, B, C)
        VALUES (2, 4, 6, 8)
        ON CONFLICT (ID)
        DO UPDATE SET A=excluded.A, B=excluded.B, C=excluded.C;

If I have a lot of rows to Insert (which I have, like thousands) I can use the multiline Insert like:

    INSERT INTO table (ID, A, B, C)
        VALUES (2, 4, 6, 8),
        ...,
        VALUES (1002, 5, 7, 9);

But is there a way to combine those two possibilities? Something like:

    INSERT INTO table (ID, A, B, C)
        VALUES (2, 4, 6, 8) ON CONFLICT (ID) DO UPDATE SET A=excluded.A, B=excluded.B, C=excluded.C,
        ...,
        VALUES (1002, 5, 7, 9) ON CONFLICT (ID) DO UPDATE SET A=excluded.A, B=excluded.B, C=excluded.C;

Additionally is there any way to get rid or shorten the List ...SET A=excluded.A, B=excluded.B, C=excluded.C;? I Would always want to replace all the Values I would insert if there is no entry yet. But I would also keep all the other entries (D to Z) if there is already an entry with the ID.

Many Thanks in advance.

insert upsert multiline sqlite
1 Answers

Instead of INSERT...VALUES use INSERT...SELECT:

INSERT INTO tablename (ID, A, B, C)
SELECT 2, 4, 6, 8 UNION ALL
SELECT 1002, 5, 7, 9
ON CONFLICT (ID) DO UPDATE 
SET A=excluded.A, B=excluded.B, C=excluded.C

Or:

SET (A, B, C)=(excluded.A, excluded.B, excluded.C)

Or with ROW VALUES instead of UNION ALL:

WITH cte AS (VALUES (2, 4, 6, 9), (1002, 5, 7, 10))
INSERT INTO tablename (ID, A, B, C)
SELECT * 
FROM cte
WHERE 1
ON CONFLICT (ID) DO UPDATE 
SET A=excluded.A, B=excluded.B, C=excluded.C
user_104988280
Related