SQLite Forum

Problem with CTE followed by a delete
Login
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