Problem with CTE followed by a delete

(1) By 4xrasy (C17_4xrasy) on 2020-04-28 07:41:26 [link] [source]

Hello,
I am not able to use a CTE in combination with a delete.
Here is the code:
    WITH RECURSIVE NODES_TO_REMOVE (CHAIN_ID, NODE, SERIAL_NUMBER, NEXT_PTR)
    AS (
                SELECT
                        CHAIN_ID,
                        NODE,
                        SERIAL_NUMBER,
                        NEXT_PTR
                FROM DCD_MODIFY_DAISY_CHAIN
                WHERE CHAIN_ID == (SELECT VALUE FROM _Variables WHERE NAME == 'CHAIN_ID')
                  AND NODE > (SELECT VALUE FROM _Variables WHERE NAME == 'NODE')
        UNION
                SELECT
                        CHAIN_ID,
                        NODE,
                        SERIAL_NUMBER,
                        NEXT_PTR
                FROM DCD_MODIFY_DAISY_CHAIN
                WHERE SERIAL_NUMBER == NEXT_PTR
    )
    DELETE FROM DAISY_CHAINED_DEVICES
        WHERE (DCD_ID == (SELECT CHAIN_ID FROM NODES_TO_REMOVE)
          AND (DCD_NODE IN (SELECT NODE FROM NODES_TO_REMOVE);

The code is part of a trigger with a trigger on a view.
There is no problem if I replace the delete with a select statement.

>Kind regards,\
c17_4xrasy

(2) By Keith Medcalf (kmedcalf) on 2020-04-28 12:50:18 in reply to 1 [link] [source]

The DELETE statement has an "unbalanced" number of parenthesis, it cannot work because it cannot be compiled. It is syntactically impossible, even if you change "DELETE FROM" to "SELECT * FROM".

(3) By 4xrasy (C17_4xrasy) on 2020-04-29 04:29:24 in reply to 2 [link] [source]

Hello,

The problem is that SQLite rejects the DELETE stat3ement in combination with the CTE. Here is the error: Error: near line 5082: near "DELETE": syntax error

SQLite is very cryptic when it reports an error. To figure out what the problem I create commonly a known error to figure out what might be causing the problem. The script contains about 7,500 lines. If the error remains the same it is probably something else…

I've tried it also without the WHERE statement and with a WHERE with balanced parenthesis. In all cases, the error remains.

Here is the code that created the error:
    -- The following statement will cause an error
    WITH RECURSIVE NODES_TO_REMOVE (CHAIN_ID, NODE, SERIAL_NUMBER, NEXT_PTR)
    AS (
                SELECT
                        CHAIN_ID,
                        NODE,
                        SERIAL_NUMBER,
                        NEXT_PTR
                FROM DCD_MODIFY_DAISY_CHAIN
                WHERE CHAIN_ID == (SELECT VALUE FROM _Variables WHERE NAME == 'CHAIN_ID')
                  AND NODE > (SELECT VALUE FROM _Variables WHERE NAME == 'NODE')
        UNION
                SELECT
                        CHAIN_ID,
                        NODE,
                        SERIAL_NUMBER,
                        NEXT_PTR
                FROM DCD_MODIFY_DAISY_CHAIN
                WHERE SERIAL_NUMBER == NEXT_PTR
    )
    DELETE FROM DAISY_CHAINED_DEVICES
        WHERE (DCD_ID == (SELECT CHAIN_ID FROM NODES_TO_REMOVE))
          AND (DCD_NODE IN (SELECT NODE FROM NODES_TO_REMOVE));

So I tried the following:
    -- The following statement will cause an error
    WITH RECURSIVE NODES_TO_REMOVE (CHAIN_ID, NODE, SERIAL_NUMBER, NEXT_PTR)
    AS (
                SELECT
                        CHAIN_ID,
                        NODE,
                        SERIAL_NUMBER,
                        NEXT_PTR
                FROM DCD_MODIFY_DAISY_CHAIN
                WHERE CHAIN_ID == (SELECT VALUE FROM _Variables WHERE NAME == 'CHAIN_ID')
                  AND NODE > (SELECT VALUE FROM _Variables WHERE NAME == 'NODE')
        UNION
                SELECT
                        CHAIN_ID,
                        NODE,
                        SERIAL_NUMBER,
                        NEXT_PTR
                FROM DCD_MODIFY_DAISY_CHAIN
                WHERE SERIAL_NUMBER == NEXT_PTR
    )
    DELETE FROM DAISY_CHAINED_DEVICES;
--        WHERE (DCD_ID == (SELECT CHAIN_ID FROM NODES_TO_REMOVE))
--          AND (DCD_NODE IN (SELECT NODE FROM NODES_TO_REMOVE));

Results in:
Error: near line 5082: near "DELETE": syntax error
QED: DELETE in combination with a CTE will FAIL!

PS: Strangely, it is impossible to use the column names defined by the CTE directly. Writing a select to access the result from the CTE is just plain silly.

XRasy

(5) By Keith Medcalf (kmedcalf) on 2020-04-29 15:16:54 in reply to 3 [link] [source]

sqlite> create table x(x);
sqlite> insert into x select value from wholenumber where value between 1 and 10;
sqlite> select * from x;
1
2
3
4
5
6
7
8
9
10
sqlite> with a(x) as (select 1 union select x+1 from a where x < 5) delete from x where x in (select x from a);
sqlite> select * from x;
6
7
8
9
10
sqlite>

Is QED short for "jumping to conclusions"? When I was a lad it was short for Quite Enough Done.

(4) By David Raymond (dvdraymond) on 2020-04-29 12:14:51 in reply to 1 [source]

The code is part of a trigger with a trigger on a view.

https://www.sqlite.org/lang_createtrigger.html

"Syntax Restrictions On UPDATE, DELETE, and INSERT Statements Within Triggers

...

Common table expression are not supported for statements inside of triggers."