SQLite Forum

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