Easist way to remove/delete dups

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

General SQL Server Forums

New to SQL Server Programming

Easist way to remove/delete dups

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-04-08 : 10:38:44

I have dups and just want them deleted from the table. Should I remove the dups and place them in another table then delete later or can I delete them from the exisiting table.

Can I do this?

delete doc, totovrSDW, totpnd, totovr, totpnd, weekdat, dowrdate
from Pending
where weekdat > 1

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-08 : 10:49:34

Which version of SQL server are you using? (I ask because depending on version there are different ways to do it)

How big is the table (how many rows)

Are the duplicates complete row duplicates? If so why don't you have a primary key?

Probably best to post the table definition and some sample data. Then explain what you want to end up with based on that data.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-08 : 10:49:48

Please show example data and wanted result.

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-08 : 10:50:14

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

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-08 : 11:06:29

quote:
Originally posted by JJ297

I have dups and just want them deleted from the table. Should I remove the dups and place them in another table then delete later or can I delete them from the exisiting table.

Can I do this?

delete doc, totovrSDW, totpnd, totovr, totpnd, weekdat, dowrdate
from Pending
where weekdat > 1

I hope this isn't a production system. What ever you do, take a backup first.

There are 10 types of people in the world, those that understand binary, and those that don't.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-08 : 11:08:31

quote:
Originally posted by JJ297

I have dups and just want them deleted from the table. Should I remove the dups and place them in another table then delete later or can I delete them from the exisiting table.

Can I do this?

delete doc, totovrSDW, totpnd, totovr, totpnd, weekdat, dowrdate
from Pending
where weekdat > 1

whats weekdat? are you sure the above query correctly identifies dups only for you?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-04-08 : 11:08:59

Someone started this table last year and didn't have keys on the table so it put duplicates into the table. So for every Friday beginning with 6/5/2009 up to 1/08/2010 has duplicates rows just like below. I changed the stored procedure and now it's bringing in the single record. I want to now go and delete those duplicates for the specific weekdat and put a key on the table.

I'm using SQL 2005


doc  totovrSDW totpndSdw totovr totpnd       weekdat                        dowrdate
007	0	0	0	2	6/5/2009 12:00:00 AM	6/26/2009 12:00:00 AM
007	0	0	0	2	6/5/2009 12:00:00 AM	6/26/2009 12:00:00 AM

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-08 : 11:15:55

quote:
Originally posted by JJ297

Someone started this table last year and didn't have keys on the table so it put duplicates into the table. So for every Friday beginning with 6/5/2009 up to 1/08/2010 has duplicates rows just like below. I changed the stored procedure and now it's bringing in the single record. I want to now go and delete those duplicates for the specific weekdat and put a key on the table.

I'm using SQL 2005

doc totovrSDW totpndSdw totovr totpnd weekdat dowrdate
007 0 0 0 2 6/5/2009 12:00:00 AM 6/26/2009 12:00:00 AM
007 0 0 0 2 6/5/2009 12:00:00 AM 6/26/2009 12:00:00 AM

Add the primary key first, then you can use it to delete the duplicated with something like this:

DELETE FROM tableName
WHERE ID NOT IN (
		SELECT MIN(ID)
		FROM tableName
		GROUP BY doc, totovrSDW, totpndSdw, totovr, totpnd, weekdat, dowrdate)

There are 10 types of people in the world, those that understand binary, and those that don't.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-08 : 11:31:06

quote:
Originally posted by JJ297

Someone started this table last year and didn't have keys on the table so it put duplicates into the table. So for every Friday beginning with 6/5/2009 up to 1/08/2010 has duplicates rows just like below. I changed the stored procedure and now it's bringing in the single record. I want to now go and delete those duplicates for the specific weekdat and put a key on the table.

I'm using SQL 2005

doc totovrSDW totpndSdw totovr totpnd weekdat dowrdate
007 0 0 0 2 6/5/2009 12:00:00 AM 6/26/2009 12:00:00 AM
007 0 0 0 2 6/5/2009 12:00:00 AM 6/26/2009 12:00:00 AM

but even then if you just look for weekdat > 1 wont they have non duplicated new data also which occured during that week?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-08 : 11:33:23

quote:
Originally posted by JJ297

Someone started this table last year and didn't have keys on the table so it put duplicates into the table. So for every Friday beginning with 6/5/2009 up to 1/08/2010 has duplicates rows just like below. I changed the stored procedure and now it's bringing in the single record. I want to now go and delete those duplicates for the specific weekdat and put a key on the table.

I'm using SQL 2005

doc totovrSDW totpndSdw totovr totpnd weekdat dowrdate
007 0 0 0 2 6/5/2009 12:00:00 AM 6/26/2009 12:00:00 AM
007 0 0 0 2 6/5/2009 12:00:00 AM 6/26/2009 12:00:00 AM

or you could just do


DELETE t
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY doc,  
totovrSDW, 
totpndSdw, 
totovr, 
totpnd,       
weekdat,                        
dowrdate
ORDER BY doc) AS Seq
FROM Table
)t
WHERE Seq>1

if using sql 2005 or above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-04-08 : 11:38:15

Thanks for all of the replies. I have a copy of this table on the development server so I can play around with it.

Let me try to delete duplicates where weekdat = '06/05/2009'

So would this work:

DELETE t
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY doc,
totovrSDW,
totpndSdw,
totovr,
totpnd,
weekdat,
dowrdate
ORDER BY doc) AS Seq
FROM Table
)t
WHERE weekdat='06/05/2009' and Seq>1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-08 : 11:41:14

quote:
Originally posted by JJ297

Thanks for all of the replies. I have a copy of this table on the development server so I can play around with it.

Let me try to delete duplicates where weekdat = '06/05/2009'

So would this work:

DELETE t
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY doc,
totovrSDW,
totpndSdw,
totovr,
totpnd,
weekdat,
dowrdate
ORDER BY doc) AS Seq
FROM Table
)t
WHERE weekdat='06/05/2009' and Seq>1

this would work but would restrict the duplicate deletion to only those records having weekdat='06/05/2009'. Are you sure you've duplicates only for the above week?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-08 : 11:44:58

quote:
Originally posted by visakh16
this would work but would restrict the duplicate deletion to only those records having weekdat='06/05/2009'. Are you sure you've duplicates only for the above week?

He's testing it with that date in a test environment before running it in production.

There are 10 types of people in the world, those that understand binary, and those that don't.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-04-08 : 11:46:41

Visakl16 you are right I still want to keep that date in the database so I would need to delete where doc='007' and weekdat='06/05/2009'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-08 : 11:47:39

quote:
Originally posted by DBA in the making

quote:
Originally posted by visakh16
this would work but would restrict the duplicate deletion to only those records having weekdat='06/05/2009'. Are you sure you've duplicates only for the above week?

He's testing it with that date in a test environment before running it in production.

There are 10 types of people in the world, those that understand binary, and those that don't.

Ok..Even in that case just made him aware

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-04-08 : 11:50:53

Yes I have duplicates all throughout and I'm on the development server so I'm okay if I mess up I will copy the production table over:

Here's more duplicates:



007	0	0	0	12	4/17/2009 12:00:00 AM	4/24/2009 12:00:00 AM
007	0	0	0	12	4/17/2009 12:00:00 AM	4/24/2009 12:00:00 AM
007	0	0	0	10	4/24/2009 12:00:00 AM	4/24/2009 12:00:00 AM
007	0	0	0	10	4/24/2009 12:00:00 AM	4/24/2009 12:00:00 AM
007	0	0	0	6	5/1/2009 12:00:00 AM	5/29/2009 12:00:00 AM
007	0	0	0	6	5/1/2009 12:00:00 AM	5/29/2009 12:00:00 AM
007	0	0	0	9	5/8/2009 12:00:00 AM	5/29/2009 12:00:00 AM
007	0	0	0	9	5/8/2009 12:00:00 AM	5/29/2009 12:00:00 AM
007	0	0	0	4	5/15/2009 12:00:00 AM	5/29/2009 12:00:00 AM
007	0	0	0	4	5/15/2009 12:00:00 AM	5/29/2009 12:00:00 AM

I don't want any duplicates dates in here for 007 so I would like to remove one row of 007 with date of 05/08/2009. Is this possible to do all at once or do I have to do it one at a time?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-08 : 11:54:50

quote:
Originally posted by JJ297

Yes I have duplicates all throughout and I'm on the development server so I'm okay if I mess up I will copy the production table over:

Here's more duplicates:

007 0 0 0 12 4/17/2009 12:00:00 AM 4/24/2009 12:00:00 AM
007 0 0 0 12 4/17/2009 12:00:00 AM 4/24/2009 12:00:00 AM
007 0 0 0 10 4/24/2009 12:00:00 AM 4/24/2009 12:00:00 AM
007 0 0 0 10 4/24/2009 12:00:00 AM 4/24/2009 12:00:00 AM
007 0 0 0 6 5/1/2009 12:00:00 AM 5/29/2009 12:00:00 AM
007 0 0 0 6 5/1/2009 12:00:00 AM 5/29/2009 12:00:00 AM
007 0 0 0 9 5/8/2009 12:00:00 AM 5/29/2009 12:00:00 AM
007 0 0 0 9 5/8/2009 12:00:00 AM 5/29/2009 12:00:00 AM
007 0 0 0 4 5/15/2009 12:00:00 AM 5/29/2009 12:00:00 AM
007 0 0 0 4 5/15/2009 12:00:00 AM 5/29/2009 12:00:00 AM

I don't want any duplicates dates in here for 007 so I would like to remove one row of 007 with date of 05/08/2009. Is this possible to do all at once or do I have to do it one at a time?

you can do all at once like


DELETE t
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY doc, 
totovrSDW, 
totpndSdw, 
totovr, 
totpnd, 
weekdat, 
dowrdate
ORDER BY doc) AS Seq
FROM Table
)t
WHERE  Seq>1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-08 : 11:58:24

If you use Visakh's solution


DELETE t
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY doc,  
totovrSDW, 
totpndSdw, 
totovr, 
totpnd,       
weekdat,                        
dowrdate
ORDER BY doc) AS Seq
FROM Table
)t
WHERE Seq>1

IT will remove ALL duplicates and leave you 1 copy of each DISTINCT row.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-04-08 : 12:05:09

Okay thanks all. Going to try it now stay tuned...

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-04-08 : 12:12:18

Thanks that did it!!! That's why I enjoy coming to this forum you guys know so much about SQL!



007	0	0	0	12	4/17/2009 12:00:00 AM	4/24/2009 12:00:00 AM
007	0	0	0	10	4/24/2009 12:00:00 AM	4/24/2009 12:00:00 AM
007	0	0	0	6	5/1/2009 12:00:00 AM	5/29/2009 12:00:00 AM
007	0	0	0	9	5/8/2009 12:00:00 AM	5/29/2009 12:00:00 AM
007	0	0	0	4	5/15/2009 12:00:00 AM	5/29/2009 12:00:00 AM
007	0	0	0	6	5/22/2009 12:00:00 AM	5/29/2009 12:00:00 AM
007	0	0	0	2	5/29/2009 12:00:00 AM	5/29/2009 12:00:00 AM

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-04-08 : 12:15:12

Okay now should I make an Identity column and put the key on there so this won't happen again?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources