CODEWITHSUNDEEP
sqlsql-servert-sqlcommon-table-expression
iAm.Hassan
iAm.Hassan

Reputation: 53

How can I use the "with" command correctly

I have a query and it works, by which I get the category subsets.

with cte as (
    Select nid 
    from TB_Category 
    where nid = 429
    union ALL
    select ca.nid 
    from cte ct 
    inner join TB_Category ca on ca.parentid = ct.nid
)
SELECT p.nid 
from TB_Category p 
inner join cte ct on ct.nid = p.nid

To delete the selected categories I use the following command but it gives an error:

DELETE FROM TB_Category 
WHERE nid in (
    with cte as (
        Select nid 
        from TB_Category 
        where nid = 429
        union ALL
        select ca.nid 
        from cte ct 
        inner join TB_Category ca on ca.parentid = ct.nid
    )
    SELECT p.nid 
    from TB_Category p 
    inner join cte ct on ct.nid = p.nid
)

Displays the following error:

Incorrect syntax near the keyword 'WHERE'.

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Incorrect syntax near the keyword 'from'.

Incorrect syntax near the keyword 'with'.

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Incorrect syntax near ')'.

Upvotes: 0

Views: 786

Answers (2)

Popeye
Popeye

Reputation: 35900

Use CTE at start of the query as follows:

with cte as (
    Select nid 
    from TB_Category 
    where nid = 429
    union ALL
    select ca.nid 
    from cte ct 
    inner join TB_Category ca on ca.parentid = ct.nid
)    
DELETE
FROM TB_Category 
WHERE nid in (
    SELECT p.nid 
    from TB_Category p 
    inner join cte ct on ct.nid = p.nid
)

Upvotes: 3

Tyron78
Tyron78

Reputation: 4187

You can delete by using a join:

WITH cte AS (
   SELECT nid 
   FROM TB_Category 
   WHERE nid= 429
   UNION ALL
   SELECT ca.nid 
   FROM cte ct 
   INNER JOIN TB_Category ca ON ca.parentid = ct.nid
)
DELETE t
FROM TB_Category t
INNER JOIN cte c ON c.nid = t.nid

But be aware, that if you have a real constraint between nid and parentid, the deletion won't work that simple - you might experience constraint violations if the deletion tries to process the parent record before the child record.

Upvotes: 1

Related Questions