A SELECT Query works fine with a subquery which SELECTS , but when DELETE is applied to the same sub query, it is not working

Question

Assuming Employee(Empid,name,salary) exists with duplicate records with Empid,name,salary in an SQL Server 2017 table.

IF OBJECT_ID('TempDB..#Employee') IS NOT NULL 
    DROP TABLE #Employee

CREATE TABLE #Employee 
(
     Empid INT, 
     name VARCHAR(250), 
     salary FLOAT
)

INSERT INTO #Employee 
VALUES  (1008, 'Biju Joseph', 8500),
        (1008, 'Biju Joseph', 8500),
        (1008, 'Haris', 9000),
        (1009, 'John', 9500),
        (1009, 'John', 9500),
        (1010, 'SMITH', 10500)

This nested query is working fine to SELECT (with SQL Server 2017):

SELECT * 
FROM 
    (SELECT 
         name,
         ROW_NUMBER() OVER (PARTITION BY name, salary ORDER BY name) cnt 
     FROM  
         employee) mytable 
WHERE 
    cnt > 1

but, when try to DELETE, it is showing an error message:

(DELETE FROM <tablename> WHERE <expression>)

DELETE FROM 
    (SELECT 
         name,
         ROW_NUMBER() OVER(PARTITION BY name, salary ORDER BY name) cnt 
     FROM 
         employee) mytable 
WHERE cnt > 1

Errors:

Msg 102, Level 15, State 1, Line 18
Incorrect syntax near '('.

Msg 102, Level 15, State 1, Line 20
Incorrect syntax near 'mytable'

Please note : when using a CTE, or other subqueries, I am able to DELETE, but what I am missing in this particular subquery, when trying to DELETE?

You have to reference the alias of the derived table in your delete. i.e delete [mytable] from (... — Xedni
– Xedni, Commented Sep 29, 2018 at 2:09

marc_s · Accepted Answer · 2018-09-29 06:13:22Z

2

You can achieve this by using the Common Table Expression(CTE).

Try the code below :

;WITH Duplicate (Name, Counts) AS
(
    SELECT 
        name, 
        ROW_NUMBER() OVER (PARTITION BY name, salary ORDER BY name) 
    FROM 
        #Employee
)
DELETE FROM Duplicate  
WHERE Counts > 1

edited Sep 29, 2018 at 6:13

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Sep 29, 2018 at 5:03

Kesava Moorthi

597 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

theMayer · Accepted Answer · 2018-09-29 01:20:30Z

1

Try this

   DELETE C
   FROM 
           (SELECT name,row_number() OVER
              (PARTITION BY name,salary
               ORDER BY name)cnt FROM employee                 
           ) C 
           where C.cnt>1

I recommend using a CTE

edited Sep 29, 2018 at 1:20

theMayer

16.3k8 gold badges65 silver badges93 bronze badges

answered Sep 29, 2018 at 1:17

Alvaro Parra

8052 gold badges9 silver badges25 bronze badges

2 Comments

SMor Over a year ago

That is not a CTE, it is a derived table. A CTE starts with "WITH" and precedes the statement (select, etc.) that references it.

Alvaro Parra Over a year ago

I know, this is a sub query as you can see but using a CTE is a better option in this case

Collectives™ on Stack Overflow

A SELECT Query works fine with a subquery which SELECTS , but when DELETE is applied to the same sub query, it is not working

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related