Sqlite3, how to make an INSERT statement inside a SELECT query?

Question

I want to do, in exactly one sqlite3 query, an operation that :

• Checks for the existence of a value in my table (call it V), written in a row indexed by A
• If V exists and is equal to V_Param (provided), insert a bunch of rows, provided as argument
• Return V

To make it clear and simple, my TABLE ( called db ) contains only (adr,v) pairs

I tried many, many, requests. But I always fail For example :

WITH 
old_value AS (
    SELECT v FROM DB WHERE adr = ?1
),
check AS (
    SELECT EXISTS(
        SELECT 1 FROM old_value 
        WHERE v = ?2 OR (v IS NULL AND ?2 IS NULL)
    ) AS check_passed
),
do_insert AS (
    SELECT 
        CASE 
            WHEN (SELECT check_passed FROM check) = 1 
            THEN (
                INSERT OR REPLACE INTO DB (adr, v)
                SELECT value1, value2
                FROM (VALUES ("a1","v1"),("a2","v2")) vals(value1, value2)
            )
        END
    WHERE (SELECT check_passed FROM check) = 1
)
SELECT v AS old_value FROM old_value;

This does not work

sqlite> .read asba2.sql
Error: near line 1: in prepare, near "check": syntax error (1)

According to these docs https://www.sqlite.org/draft/lang_select.html I can not do an insert in a SELECT.

Is there a way to do what I want in one statement ?

Answer 1

With an updatable view

Let's summarize the constraints shown in my first answer and comments:

1. SQLite allows INSERT only as the main query, not in a CTE (even with RETURNING)
   → we need an INSERT OR REPLACE SELECT
2. the return is expected to be only 1 row, while the insert or update can cover from 0 to n rows; but SQLite's RETURNING returns as many rows as inserted / updated → we cannot insert into db, only into a guard-like table or view
3. as I discovered, INSERT RETURNING on an updatable view returns the originally passed value instead of the newly viewed one
   → we even don't need to make the old v appear in the view to return old_value (at first I thought we needed to add a last_v column to db, or create a secondary db_last table to hold the last value, but for your use case this is not necessary)
4. with your last constraint (return 1 row if an adr matches, would the guard succeed or fail; but return 0 row if no adr is found), we cannot filter on the right guard value as soon as the INSERT SELECT
   → the top level INSERT SELECT's WHERE will only look for an existing guard, but it will not discard on bad guard, only pass a noop order to the (trigger's) inner INSERT.

So we'll have a view, with a TRIGGER INSTEAD OF INSERT, called by an INSERT RETURNING query:

• our updatable view will have at least:
  • a v
    so that it can be read back as old_value
  • a to_insert column
    through which the values to INSERT OR REPLACE will be transmitted (for example in JSON)
• but it doesn't need to know if the guard passed or blocked:
  an empty set of modifications has the same effects as a guard block
• and thus the adr isn't needed in the view

All in all, with a preparatory work:

CREATE VIEW guard_and_add AS
    SELECT v guardv, JSON_ARRAY() to_insert
    FROM db;

CREATE TRIGGER guard_and_add
INSTEAD OF INSERT ON guard_and_add
BEGIN
    INSERT OR REPLACE INTO db (adr, v)
        SELECT r.value->>'value1', r.value->>'value2'
        FROM json_each(NEW.to_insert) r
    ;
END;

We can make a pseudo-insert that guards, inserts, and returns in one call:

INSERT INTO guard_and_add
WITH
    ins AS
    (
        SELECT ?1 ga, ?2 gv,
        JSON('[{"value1":"a1","value2":"v1"},{"value1":"a2","value2":"v2"}]') ins
    )
    SELECT guard.v, case when guard.v = ins.gv then ins else JSON_ARRAY() end
    FROM ins, db guard
    WHERE guard.adr = ins.ga
RETURNING guardv;

Here is a fiddle with 2 attempts, one succeeding (and modifying the entry used as a guard, as well as "neutral" entries), and one rejected (due to use of the old token, while it has just been modified).

Answer 2

Once corrected the check pointed by @Barmar,
and although the whole logic looks strange (see my comment),
you seem to look for an INSERT OR REPLACE … RETURNING values from another table.

However, as @Barmar showed in another comment (and as explicitely stated in SQLite discussions directing to item 3 of the official "Limitations and Caveats" doc),
you cannot use an INSERT in a CTE.

You could transform your query to an INSERT OR REPLACE … SELECT … RETURNING, but it won't work if your RETURNING should contain fields from another table.

In that case you could use a temporary table to planify all your operations + return values,
and then separately run the INSERT OR REPLACE and the final SELECT:

CREATE TEMP TABLE ior AS
WITH
old_value AS (
    SELECT v FROM DB WHERE adr = ?1
),
check_ AS (
    SELECT EXISTS(
        SELECT 1 FROM old_value 
        WHERE v = ?2 OR (v IS NULL AND ?2 IS NULL)
    ) AS check_passed
),
vals(value1, value2) AS (VALUES ('a1','v1'),('a2','v2'))
SELECT vals.value1 adr, DB.v old_val, vals.value2 new_val
FROM vals LEFT JOIN DB ON DB.adr = vals.value1
WHERE (SELECT check_passed FROM check_) = 1;

INSERT OR REPLACE INTO DB (adr, v)
SELECT adr, new_val FROM ior;

SELECT adr, old_val old_value FROM ior;

See it in a DBFiddle
(or that simpler one if the intention is to check row by row)