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