delete duplicate records - 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 records

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-06-15 : 06:13:05

i have a table that has many duplicate records

the fields id,autodate,dateentered are different but all other fields are the same -- how can i delete all the duplicates and leave just the last record entered? (only to delete if all fields match except id,autodate and dateentered)
whats' the easiest way to do this?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-15 : 06:17:29

Something like this:
delete dt
from
(
select row_number() over (partition by othercol1,othercol2,othercol3 order by dateentered DESC) as rownum,
*
from your_table
)dt
where rownum > 1

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-15 : 06:18:51

select * from
(
select row_number() over(partition by id,autodate,dateentered order by dateentered desc) as sn,* from your_table
) as t
where sno>1

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-15 : 06:28:29

quote:
Originally posted by madhivanan

select * from
(
select row_number() over(partition by id,autodate,dateentered order by dateentered desc) as sno,* from your_table
) as t
where sno=1

Madhivanan

Failing to plan is Planning to fail

the fields id,autodate,dateentered are different
So each record will get sno = 1

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-15 : 06:32:05

quote:
Originally posted by webfred

quote:
Originally posted by madhivanan

select * from
(
select row_number() over(partition by id,autodate,dateentered order by dateentered desc) as sno,* from your_table
) as t
where sno=1

Madhivanan

Failing to plan is Planning to fail

the fields id,autodate,dateentered are different
So each record will get sno = 1

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

Thanks. I changed it

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-15 : 06:36:04

Sorry but I see no changes and btw. it is already my solution...

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-15 : 06:37:48

quote:
Originally posted by webfred

Sorry but I see no changes and btw. it is already my solution...

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

I made it as a SELECT statement whose result, after verification,should be deleted

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-15 : 06:41:04

Yes but
partition by id,autodate,dateentered
will give each record sno=1
because id,autodate,dateentered are different in each record as OP stated!

Have a look at my provided solution please and tell me what is wrong with it.

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-15 : 06:44:11

quote:
Originally posted by webfred

Yes but
partition by id,autodate,dateentered
will give each record sno=1
because id,autodate,dateentered are different in each record as OP stated!

Have a look at my provided solution please and tell me what is wrong with it.

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

You are correct. I missed to note that point
Partition clause should include other columns only

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-06-15 : 06:55:34

webfred - yours works a select statement

select *
from
(
select row_number() over (partition by othercol1,othercol2,othercol3 order by dateentered DESC) as rownum,
*
from your_table
)dt
where rownum > 1

but when i do

delete dt
from
(
select row_number() over (partition by othercol1,othercol2,othercol3 order by dateentered DESC) as rownum,
*
from your_table
)dt
where rownum > 1

I get an error Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ')'.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-15 : 07:55:42

Have a look at YOUR executed statement.
Are there parenthesis missing?

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

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-06-15 : 07:59:24

no if i replace delete dt from with select * from - it works

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-15 : 08:05:42