January 24, 2012 at 3:28 am
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 --
January 24, 2012 at 5:21 am
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.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
January 24, 2012 at 5:25 am
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.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 24, 2012 at 2:05 pm
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
FROMPost
JOINcte
ONPost.Path LIKE '%' + cte.Path + '%'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply