CODEWITHSUNDEEP
sqlsql-serversql-server-2008
President Camacho
President Camacho

Reputation: 1910

How to get DELETE FROM [QUERY] to work?

Hi I'm kinda new to mssql, I'm used to Oracle. I'm trying to delete a specific row from a subquery but mssql doens't really like subqueries.

Here is the query: 

DELETE FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column1) row FROM randomtable) a
WHERE a.row = 1

Is there a way to get this to work?

In Oracle I could've get everything in a query because I can use rownum = 1.

Upvotes: 2

Views: 99

Answers (3)

Fortunato
Fortunato

Reputation: 1350

Write the alias DELETE a FROM ...

Upvotes: 1

Bill Gregg
Bill Gregg

Reputation: 7147

You didn't specify the table alias in what you wanted to delete from.

DELETE a FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column1) row FROM randomtable) a
WHERE a.row = 1

Upvotes: 3

Martin Smith
Martin Smith

Reputation: 453192

You were nearly there

DELETE a
FROM   (SELECT *,
               ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column1) row
        FROM   randomtable) a
WHERE  a.row = 1

Though I prefer the CTE syntax

WITH a
     AS (SELECT *,
                ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column1) row
         FROM   randomtable)
DELETE FROM a
WHERE  a.row = 1

Upvotes: 3

Related Questions