SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Remove a Simple Duplicate


Remove a Simple Duplicate

Author
Message
AndrewSQLDBA
AndrewSQLDBA
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4366 Visits: 3427
Hello Everyone
I found some duplicate data as I was going thru the logic of a data pump. The entire row is not duplicated however.

I would like to delete only the one row.

This is a sample of the data:

DECLARE @SomeData TABLE
(
FirstName varchar(25)
, MiddleName varchar(25)
, LastName varchar(25)
, StreetAddress varchar(25)
, Suite varchar(25)
, City varchar(25)
, [State] varchar(25)
, PostalCode varchar(10)
, AreaCode varchar(5)
, PhoneNumber varchar(8)
)
INSERT INTO @SomeData
SELECT 'Joe','Al','Smith','123 Main Street',NULL,'Dallas', 'North Carolina', '45789', NULL, NULL UNION ALL
SELECT 'Joe','Al','Smith','123 Main Street',NULL,'Dallas', 'North Carolina', '45789', '256', '124-5879' UNION ALL
SELECT 'Sally','Ann','Smith','123 Main Street',NULL,'Dallas', 'North Carolina', '45789', '256', '124-5879' UNION ALL
SELECT 'Steven','David','Smith','123 Main Street',NULL,'Dallas', 'North Carolina', '45789', '256', '124-5879'

SELECT * FROM
@SomeData



As you can see, Joe Smith has two rows, but only one of the rows is complete. I would like to delete only the row that has a NULL value in the phone and area code for Joe Smith. There are a few thousand rows that are like this. They have duplicates all but the area code and phone number.

I am used to using a CTE to remove duplicates, but I am a little lost on this one. The things that I have tried, have not worked exactly as I planned.

I hope that someone has a better way.

Thank you in advance for your time, assistance, and suggestions

Andrew SQLDBA
SQLBlimp
SQLBlimp
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1649 Visits: 508
I'd stage into a temporary table with an identity column and delete from the temp table where

1. The phone number or area code are null
2. And the name and address columns exist elsewhere with a different identity than the current row.

Did I capture the conditions correctly?

Thanks
John.
AndrewSQLDBA
AndrewSQLDBA
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4366 Visits: 3427
Thank You John
That is what I tried using a CTE, but I am not getting something correct. I am having trouble with select the correct row to delete.

Would you offer a sample of code?

Thanks
Andrew
AlexSQLForums
AlexSQLForums
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3496 Visits: 2283
If you only see nulls in areacode and phonenumber you can get rid of them with temp table like john mentioned

select into #t1
from @somedata where AreaCode is not null and PhoneNumber is not null



Alex S
a4apple
a4apple
Mr or Mrs. 500
Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)

Group: General Forum Members
Points: 504 Visits: 406
something like this... may be you need to modify it accordingly..

DECLARE @SomeData TABLE
(
FirstName varchar(25)
, MiddleName varchar(25)
, LastName varchar(25)
, StreetAddress varchar(25)
, Suite varchar(25)
, City varchar(25)
, [State] varchar(25)
, PostalCode varchar(10)
, AreaCode varchar(5)
, PhoneNumber varchar(8)
)
INSERT INTO @SomeData
SELECT 'Joe','Al','Smith','123 Main Street',NULL,'Dallas', 'North Carolina', '45789', NULL, NULL UNION ALL
SELECT 'Joe','Al','Smith','123 Main Street',NULL,'Dallas', 'North Carolina', '45789', '256', '124-5879' UNION ALL
SELECT 'Sally','Ann','Smith','123 Main Street',NULL,'Dallas', 'North Carolina', '45789', '256', '124-5879' UNION ALL
SELECT 'Steven','David','Smith','123 Main Street',NULL,'Dallas', 'North Carolina', '45789', '256', '124-5879'

;WITH CTE AS
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY FirstName, MiddleName, LastName Order by AreaCode DESC, PhoneNumber DESC) AS Rnum FROM @SomeData
)
--SELECT * FROM CTE
DELETE FROM CTE
--WHERE Rnum = 1
WHERE Rnum <> 1

SELECT * FROM @SomeData



http://sqlsaga.com/sql-server/how-to-remove-duplicates-from-a-table-in-sql-server/

Good Luck Smile .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search