SQLite Forum

Problem with CTE followed by a delete
Login

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 [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 [link] [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."