Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Remove a Simple Duplicate Expand / Collapse
Author
Message
Posted Thursday, May 1, 2014 10:26 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, November 7, 2014 2:00 PM
Points: 979, Visits: 3,364
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
Post #1566771
Posted Thursday, May 1, 2014 10:40 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, December 5, 2014 9:21 AM
Points: 776, Visits: 337
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.
Post #1566783
Posted Thursday, May 1, 2014 10:53 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, November 7, 2014 2:00 PM
Points: 979, Visits: 3,364
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
Post #1566789
Posted Thursday, May 1, 2014 12:01 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 1:41 PM
Points: 1,298, Visits: 1,891
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
Post #1566814
Posted Thursday, May 1, 2014 12:09 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 5:53 PM
Points: 143, Visits: 316
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 :) .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
Post #1566818
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse