Delete duplicate row (using alias in where clause)

Transact-SQL
1

Hi ,

Pls go through below code. I want to delete duplicate record from the table (by using alias name in where clause)

In the table there is only one column. column name is 'Data'. there are 3 row in that column. 'not duplicate row','duplicate row', 'duplicate row'. i want to delete one row i.e. one 'duplicate row' record in 'Data' column

My Query is:

delete from (select *,
row_number() over(partition by data order by data) as duplicates
from abc) as xyz where duplicates>1

Note: Please make modifications in above query only. i no need CTE, WITH Statement or other.

Thank you in advance.

2 
delete a
  from (select [data]
              ,row_number() over(partition by [data]
                                     order by (select null)
                                )
               as rn
          from abc
       ) as a
 where a.rn>1
;
3

bitsmed, I have one doubt. Actually delete command syntanx is : delete from table where .

But in your code, you mentioned table alias name before 'from' in outer query. How do i understand this. Pls explain.

4

When you have a subselect, you need to assign an aliasname to it, and when using alias, the delete requires you use that aliasname the way I showed.

5

You have my curiosity up... why can't you use a CTE? Are you using something other than SQL Server or a very old version of SQL Server or ????