CODEWITHSUNDEEP
sqlsql-servert-sql
GuidoG
GuidoG

Reputation: 12014

Incorrect syntax when using an CTE

I am trying to find double records in a table.
My goal is to examine the result first, and if ok then delete the doubles.

So the idea is to use a CTE to find all doubles, and leaving out the original record

WITH CTE AS(
   SELECT ChassisID, FileName, OpdrachtID,
      RN = ROW_NUMBER() OVER (PARTITION BY ChassisID, FileName, OpdrachtID ORDER BY ChassisID)
   FROM dbo.tblChassisVMac
)
SELECT FROM CTE 
WHERE RN > 1

and after examining if all is correct I wanted to replace the SELECT by a DELETE

But I keep getting this error :

Incorrect syntax near the keyword 'FROM'

I probably am missing something trivial but I just cant see it

SQL Server 2014 (SP2)

Upvotes: 0

Views: 855

Answers (4)

Bojan
Bojan

Reputation: 1

When CTE is used, it has to be the first statement in a batch. Try using derived tables instead, as follows

IF @Block <> 'All'
BEGIN
SELECT * FROM (SELECT Columns FROM TableName) AS T
END

ELSE
BEGIN
SELECT * FROM (SELECT Columns FROM TableName) AS T1

Upvotes: 0

Ravi
Ravi

Reputation: 1172

use * before from keyword or use column name in select Query 

  ;WITH CTE AS(
       SELECT ChassisID, FileName, OpdrachtID,
          RN = ROW_NUMBER() OVER (PARTITION BY ChassisID, FileName, OpdrachtID ORDER BY ChassisID)
       FROM dbo.tblChassisVMac
    )
    SELECT *
    FROM CTE 
    WHERE RN > 1

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521409

Your delete would look like this:

DELETE
FROM CTE
WHERE RN > 1;

A DELETE statement does not normally specify columns, because it targets entire records for deletion. On the other hand, a SELECT query needs to specify columns. So the following select would work:

SELECT *
FROM CTE
WHERE RN > 1;

Upvotes: 3

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You need to define at least one expression or column with SELECT statement 

SELECT col1, . . .
FROM CTE 
WHERE RN > 1

You can delete the records as :

DELETE C
FROM CTE C
WHERE RN > 1;

Upvotes: 2

Related Questions