delete duplicate row - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

delete duplicate row

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-01-14 : 06:38:55

Hi,
tblMain has three fields. ID, Field2, field3

After several inserts into tblMain, it has duplicate rows.
I am trying to delete the extra row using row_number() as follows but the error I get is:
invalid object name 'CTE tblResult'

; with [CTE tblResult]
as
(
select
RowNum = row_number() over (partitioin by ID, order by ID),
field2,
field3
from
tblMain
)

delete from [CTE tblResult] where RowNum > 1

delete tblMain

insert into tblMain
select * from [CTE tblResult]

Any thoughts on what I am doing wrong please?

Thanks

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-14 : 06:42:09

You can't use a CTE like that but you can use a physical temp table do the same.

select
RowNum = row_number() over (partitioin by ID, order by ID),
field2,
field3
INTO #temp
from
tblMain

Delete from #temp where RowNum >1

TRUNCATE TABLE tblMain

insert into tblMain
Select * From #temp

Poor planning on your part does not constitute an emergency on my part.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-14 : 07:34:30

You can directly delete the dups like this:

delete dt
from
(
select
RowNum = row_number() over (partition by ID, order by ID),
field2,
field3
from
tblMain
) as dt
where RowNum > 1

No, you're never too old to Yak'n'Roll if you're too young to die.

Sachin.Nand

2937 Posts

Posted - 2011-01-14 : 08:26:06

quote:
; with [CTE tblResult]
as
(
select
RowNum = row_number() over (partitioin by ID, order by ID),
field2,
field3
from
tblMain
)

delete from [CTE tblResult] where RowNum > 1

Deleting from [CTE tblResult] is actually going to delete from tblmain.
So just put an insert clause after the query I have quoted.This will suffice your requirement.

PBUH

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-01-14 : 09:10:59

Thank you all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources