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

madscientist
Starting Member

30 Posts

Posted - 2008-05-07 : 20:30:43

Hello everyone,

I have a little dilemma. I have a table ALLTABLE that has duplicate records and I want to delete them. ALLTABLE has these columns with these values for example:

Policy Premium Class State Entity Number
ADC-WC-0010005-0 25476 63 31 1
ADC-WC-0010005-0 25476 63 31 2
ADC-WC-0010005-0 25476 63 31 3
ADC-WC-0010005-0 1457 63 29 4
ADC-WC-0010092-1 2322 63 37 1
ADC-WC-0010344-0 515 63 01 1
ADC-WC-0010344-0 515 63 01 2

As you can see there is some duplicates in the first 3 rows and the final 2 (the entity number is the only difference). I want the table to look like this:

Policy Premium Class State Entity Number
ADC-WC-0010005-0 25476 63 31 1
ADC-WC-0010005-0 1457 63 29 4
ADC-WC-0010092-1 2322 63 37 1
ADC-WC-0010344-0 515 63 01 1

Thank you so much for the help. It is really appreciated.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-05-07 : 21:13:16

quick and dirty:

select distinct * into #temp from alltable
truncate table alltable
insert alltable select * from #temp

elsasoft.org

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-05-07 : 21:19:54

>> select distinct * into #temp from alltable

That will give you all rows. Try something like:

DELETE t1
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col2 DESC) AS RecID
FROM Table1
) AS t1
WHERE RecID > 1

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-05-07 : 22:42:35

ah, missed that last column. I had thought the entire row was duped.

elsasoft.org

madscientist
Starting Member

30 Posts

Posted - 2008-05-08 : 13:10:49

Thank you jezemine and rmiao for your helpful responses. You saved the day!!!

madscientist
Starting Member

30 Posts

Posted - 2008-05-08 : 17:07:24

Hey sorry to bug you guys again. With the same dilemma but now I want to make the duplicates where the Premium column is 0. I tried to format the example a little better lol. This is the original:

Policy.............Premium...Class...State...Entity Number
ADC-WC-0010005-0.....25476......63......31...............1
ADC-WC-0010005-0.....25476......63......31...............2
ADC-WC-0010005-0.....25476......63......31...............3
ADC-WC-0010005-0......1457......63......29...............4
ADC-WC-0010092-1......2322......63......37...............1
ADC-WC-0010344-0.......515......63......01...............1
ADC-WC-0010344-0.......515......63......01...............2

The first 4 rows are the same policy but with 2 different states. So out of the first 4 rows, 3 are the same so there should be 2 rows set to 0. The bottom 2 rows are the same so 1 of the premiums should be set to 0. I want the table to look like this:

Policy.............Premium...Class...State...Entity Number
ADC-WC-0010005-0.....25476......63......31...............1
ADC-WC-0010005-0.........0......63......31...............2
ADC-WC-0010005-0.........0......63......31...............3
ADC-WC-0010005-0......1457......63......29...............4
ADC-WC-0010092-1......2322......63......37...............1
ADC-WC-0010344-0.......515......63......01...............1
ADC-WC-0010344-0.........0......63......01...............2

Thank you again for the help. It is really appreciated.

madscientist
Starting Member

30 Posts

Posted - 2008-05-08 : 20:03:04

I forgot to mention that a policy can also have the same entity number so for example:

Policy.............Premium...Class...State...Entity Number
ADC-WC-0010005-0.....25476......63......31...............1
ADC-WC-0010005-0.....25476......63......31...............1
ADC-WC-0010005-0.....25476......63......31...............2
ADC-WC-0010005-0.....25476......63......31...............3
ADC-WC-0010005-0......1457......63......29...............4
ADC-WC-0010092-1......2322......63......37...............1
ADC-WC-0010344-0.......515......63......01...............1
ADC-WC-0010344-0.......515......63......01...............2

but I still need to only have 1 premium value for each individual policy and state for example

Policy.............Premium...Class...State...Entity Number
ADC-WC-0010005-0.....25476......63......31...............1
ADC-WC-0010005-0.........0......63......31...............1
ADC-WC-0010005-0.........0......63......31...............2
ADC-WC-0010005-0.........0......63......31...............3
ADC-WC-0010005-0......1457......63......29...............4
ADC-WC-0010092-1......2322......63......37...............1
ADC-WC-0010344-0.......515......63......01...............1
ADC-WC-0010344-0.........0......63......01...............2

Please anyone help!!! LOL Thank you so much!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-09 : 05:04:47

quote:
Originally posted by madscientist

I forgot to mention that a policy can also have the same entity number so for example:

Policy.............Premium...Class...State...Entity Number
ADC-WC-0010005-0.....25476......63......31...............1
ADC-WC-0010005-0.....25476......63......31...............1
ADC-WC-0010005-0.....25476......63......31...............2
ADC-WC-0010005-0.....25476......63......31...............3
ADC-WC-0010005-0......1457......63......29...............4
ADC-WC-0010092-1......2322......63......37...............1
ADC-WC-0010344-0.......515......63......01...............1
ADC-WC-0010344-0.......515......63......01...............2

but I still need to only have 1 premium value for each individual policy and state for example

Policy.............Premium...Class...State...Entity Number
ADC-WC-0010005-0.....25476......63......31...............1
ADC-WC-0010005-0.........0......63......31...............1
ADC-WC-0010005-0.........0......63......31...............2
ADC-WC-0010005-0.........0......63......31...............3
ADC-WC-0010005-0......1457......63......29...............4
ADC-WC-0010092-1......2322......63......37...............1
ADC-WC-0010344-0.......515......63......01...............1
ADC-WC-0010344-0.........0......63......01...............2

Please anyone help!!! LOL Thank you so much!

Where do you want to show the data?
If you use reports, make use of suppress if duplicated feature

Madhivanan

Failing to plan is Planning to fail

madscientist
Starting Member

30 Posts

Posted - 2008-05-09 : 12:12:01

quote:
Originally posted by madhivanan

quote:
Originally posted by madscientist

I forgot to mention that a policy can also have the same entity number so for example:

Policy.............Premium...Class...State...Entity Number
ADC-WC-0010005-0.....25476......63......31...............1
ADC-WC-0010005-0.....25476......63......31...............1
ADC-WC-0010005-0.....25476......63......31...............2
ADC-WC-0010005-0.....25476......63......31...............3
ADC-WC-0010005-0......1457......63......29...............4
ADC-WC-0010092-1......2322......63......37...............1
ADC-WC-0010344-0.......515......63......01...............1
ADC-WC-0010344-0.......515......63......01...............2

but I still need to only have 1 premium value for each individual policy and state for example

Policy.............Premium...Class...State...Entity Number
ADC-WC-0010005-0.....25476......63......31...............1
ADC-WC-0010005-0.........0......63......31...............1
ADC-WC-0010005-0.........0......63......31...............2
ADC-WC-0010005-0.........0......63......31...............3
ADC-WC-0010005-0......1457......63......29...............4
ADC-WC-0010092-1......2322......63......37...............1
ADC-WC-0010344-0.......515......63......01...............1
ADC-WC-0010344-0.........0......63......01...............2

Please anyone help!!! LOL Thank you so much!

Where do you want to show the data?
If you use reports, make use of suppress if duplicated feature

Madhivanan

Failing to plan is Planning to fail

Hi Madhivanan. I don't need to show this data anywhere. It is just in a table. I export it to a text file afterwards.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-09 : 12:17:52

quote:
Originally posted by madscientist

Hey sorry to bug you guys again. With the same dilemma but now I want to make the duplicates where the Premium column is 0. I tried to format the example a little better lol. This is the original:

Policy.............Premium...Class...State...Entity Number
ADC-WC-0010005-0.....25476......63......31...............1
ADC-WC-0010005-0.....25476......63......31...............2
ADC-WC-0010005-0.....25476......63......31...............3
ADC-WC-0010005-0......1457......63......29...............4
ADC-WC-0010092-1......2322......63......37...............1
ADC-WC-0010344-0.......515......63......01...............1
ADC-WC-0010344-0.......515......63......01...............2

The first 4 rows are the same policy but with 2 different states. So out of the first 4 rows, 3 are the same so there should be 2 rows set to 0. The bottom 2 rows are the same so 1 of the premiums should be set to 0. I want the table to look like this:

Policy.............Premium...Class...State...Entity Number
ADC-WC-0010005-0.....25476......63......31...............1
ADC-WC-0010005-0.........0......63......31...............2
ADC-WC-0010005-0.........0......63......31...............3
ADC-WC-0010005-0......1457......63......29...............4
ADC-WC-0010092-1......2322......63......37...............1
ADC-WC-0010344-0.......515......63......01...............1
ADC-WC-0010344-0.........0......63......01...............2

Thank you again for the help. It is really appreciated.

Try this:-

UPDATE t
SET t.Premium=0
FROM(SELECT ROW_NUMBER() OVER (PARTITION BY Policy,Premium ORDER BY EntityNumber) AS RowNo,
*
FROM YourTable)t
WHERE t.RowNo <>1

madscientist
Starting Member

30 Posts

Posted - 2008-05-09 : 12:28:07

quote:
Originally posted by visakh16

quote:
Originally posted by madscientist

Hey sorry to bug you guys again. With the same dilemma but now I want to make the duplicates where the Premium column is 0. I tried to format the example a little better lol. This is the original:

Policy.............Premium...Class...State...Entity Number
ADC-WC-0010005-0.....25476......63......31...............1
ADC-WC-0010005-0.....25476......63......31...............2
ADC-WC-0010005-0.....25476......63......31...............3
ADC-WC-0010005-0......1457......63......29...............4
ADC-WC-0010092-1......2322......63......37...............1
ADC-WC-0010344-0.......515......63......01...............1
ADC-WC-0010344-0.......515......63......01...............2

The first 4 rows are the same policy but with 2 different states. So out of the first 4 rows, 3 are the same so there should be 2 rows set to 0. The bottom 2 rows are the same so 1 of the premiums should be set to 0. I want the table to look like this:

Policy.............Premium...Class...State...Entity Number
ADC-WC-0010005-0.....25476......63......31...............1
ADC-WC-0010005-0.........0......63......31...............2
ADC-WC-0010005-0.........0......63......31...............3
ADC-WC-0010005-0......1457......63......29...............4
ADC-WC-0010092-1......2322......63......37...............1
ADC-WC-0010344-0.......515......63......01...............1
ADC-WC-0010344-0.........0......63......01...............2

Thank you again for the help. It is really appreciated.

Try this:-

UPDATE t
SET t.Premium=0
FROM(SELECT ROW_NUMBER() OVER (PARTITION BY Policy,Premium ORDER BY EntityNumber) AS RowNo,
*
FROM YourTable)t
WHERE t.RowNo <>1

It seems to have worked! Thank you visakh16 for all of your help. You have come through for me in the past and I really appreciate it. Thank you as well to the others for all the help. This forum is awesome!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-09 : 12:31:25

quote:
Originally posted by madscientist

quote:
Originally posted by visakh16

quote:
Originally posted by madscientist

Hey sorry to bug you guys again. With the same dilemma but now I want to make the duplicates where the Premium column is 0. I tried to format the example a little better lol. This is the original:

Policy.............Premium...Class...State...Entity Number
ADC-WC-0010005-0.....25476......63......31...............1
ADC-WC-0010005-0.....25476......63......31...............2
ADC-WC-0010005-0.....25476......63......31...............3
ADC-WC-0010005-0......1457......63......29...............4
ADC-WC-0010092-1......2322......63......37...............1
ADC-WC-0010344-0.......515......63......01...............1
ADC-WC-0010344-0.......515......63......01...............2

The first 4 rows are the same policy but with 2 different states. So out of the first 4 rows, 3 are the same so there should be 2 rows set to 0. The bottom 2 rows are the same so 1 of the premiums should be set to 0. I want the table to look like this:

Policy.............Premium...Class...State...Entity Number
ADC-WC-0010005-0.....25476......63......31...............1
ADC-WC-0010005-0.........0......63......31...............2
ADC-WC-0010005-0.........0......63......31...............3
ADC-WC-0010005-0......1457......63......29...............4
ADC-WC-0010092-1......2322......63......37...............1
ADC-WC-0010344-0.......515......63......01...............1
ADC-WC-0010344-0.........0......63......01...............2

Thank you again for the help. It is really appreciated.

Try this:-

UPDATE t
SET t.Premium=0
FROM(SELECT ROW_NUMBER() OVER (PARTITION BY Policy,Premium ORDER BY EntityNumber) AS RowNo,
*
FROM YourTable)t
WHERE t.RowNo <>1

It seems to have worked! Thank you visakh16 for all of your help. You have come through for me in the past and I really appreciate it. Thank you as well to the others for all the help. This forum is awesome!!!

You're welcome

. Glad that i could help u on this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources