cte delete operation

Question

cte delete operation

kirkdm01

SSC-Addicted

Points: 471
More actions
January 24, 2012 at 3:28 am

#249545

I have a cte that loops through the records creates a list and i pass a parameter of @ParamterID in a select statement but the set operations says incorrext syntax near Set and in that select it gets the path where I can do a search for the paths that are LIKE '%' + @Path + '%' and those are the items I want to delete from the table called Post. could someone help me complete this please and correct my SET @Path syntax
DELCARE @ParmeterID int = 3
DECLARE @Path nvarchar(MAX)
WITH cte(Url, Level, Path, PostID, ParentPostID)
AS (
SELECT REPLACE(LOWER(Title), ' ', '-'), 1 AS Level, CAST(PostID as nvarchar(MAX)) as Path,
PostID, ParentPostID
FROM Post
WHERE ParentPostID IS NULL
UNION ALL
SELECT cte.Url, cte.Level + 1,
LOWER(cte.Path + '-' + CAST(P.PostID AS VARCHAR(MAX))),
P.PostID, P.ParentPostID
FROM Post P
INNER JOIN cte ON P.ParentPostID = cte.PostID
)
-- says incorrect sybnatx near Set @Path
SET @Path = (SELECT Path FROM cte WHERE PostID = @ParemeterID);
-- list i want to delete --
SELECT * FROM cte WHERE Path LIKE '%' + @Path + '%'
delete -- list -- FROM Post --

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

kirkdm01 (1/24/2012)
I have a cte that loops through the records creates a list and i pass a parameter of @ParamterID in a select statement but the set operations says incorrext syntax near Set and in that select it gets the path where I can do a search for the paths that are LIKE '%' + @Path + '%' and those are the items I want to delete from the table called Post. could someone help me complete this please and correct my SET @Path syntax
DELCARE @ParmeterID int = 3
DECLARE @Path nvarchar(MAX)
WITH cte(Url, Level, Path, PostID, ParentPostID)
AS (
SELECT REPLACE(LOWER(Title), ' ', '-'), 1 AS Level, CAST(PostID as nvarchar(MAX)) as Path,
PostID, ParentPostID
FROM Post
WHERE ParentPostID IS NULL
UNION ALL
SELECT cte.Url, cte.Level + 1,
LOWER(cte.Path + '-' + CAST(P.PostID AS VARCHAR(MAX))),
P.PostID, P.ParentPostID
FROM Post P
INNER JOIN cte ON P.ParentPostID = cte.PostID
)
-- says incorrect sybnatx near Set @Path
SET @Path = (SELECT Path FROM cte WHERE PostID = @ParemeterID);
-- list i want to delete --
SELECT * FROM cte WHERE Path LIKE '%' + @Path + '%'
delete -- list -- FROM Post --

There is probably a typo( DELCARE instead of DECLARE ) and a syntax error( a ";" should always precede "WITH" keyword in a CTE ) I could find as of now

DECLARE @ParmeterID int = 3

DECLARE @Path nvarchar(MAX)

;WITH cte(Url, Level, Path, PostID, ParentPostID)

AS (

SELECT REPLACE(LOWER(Title), ' ', '-'), 1 AS Level, CAST(PostID as nvarchar(MAX)) as Path,

PostID, ParentPostID

FROM Post

WHERE ParentPostID IS NULL

UNION ALL

SELECT cte.Url, cte.Level + 1,

LOWER(cte.Path + '-' + CAST(P.PostID AS VARCHAR(MAX))),

P.PostID, P.ParentPostID

FROM Post P

INNER JOIN cte ON P.ParentPostID = cte.PostID

)

-- says incorrect sybnatx near Set @Path

SET @Path = (SELECT Path FROM cte WHERE PostID = @ParemeterID);

-- list i want to delete --

SELECT * FROM cte WHERE Path LIKE '%' + @Path + '%'

delete -- list -- FROM Post --

Some sample data and DDL will be of help here. Please read the link in my signature on how to provide the same. If you provide it, I am sure your issue will be solved in no time.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 2

kirkdm01 (1/24/2012)
I have a cte that loops through the records creates a list and i pass a parameter of @ParamterID in a select statement ...

Check that your rCTE works before increasing the complexity any further. You will need to correct a few typos as Kingston has pointed out, also ensure that columns are named in the anchor part of the rCTE.

Note that rCTE's aren't updatable because they contain a UNION operator - once you've got the rCTE working correctly, run the results into a #temp table and join that to your source table to target the deletes.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

fahey.jonathan Hall of Fame Points: 3617 More actions · Answer 3

A CTE works only with a single statement. You appear to be trying to use several statements with the CTE; the SET of a variable, then a SELECT, then a DELETE. The SET will not work because the compiler is expecting a query, not a variable operation. After the CTE definition, you may choose only a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. These statements may be complex, but only one is allowed.

It seems you want to delete from the "Post" table based on the "path" column generated in the CTE. I suggest that you join the CTE to the Post table and delete. Something like:

WITH cte

AS(

{{your cte definion}}

)

DELETE

FROMPost

JOINcte

ONPost.Path LIKE '%' + cte.Path + '%'