Solved

Need to delete duplicate records

Posted on 2016-08-20
7
54 Views
Last Modified: 2016-08-24
I was having problems with a table and I realized that there are some duplicate records that need to be deleted.
How do I do this if they are exact copies?

STOREKEY          |STORENUM|BNMB|STRT
NY1S05000010 |1001             |10       |Spring Street
NY1S05000010 |1001             |10       |Spring Street

I need to get rid of the 2nd one.  The dupes are not all over the file.  In other words, It only happens occasionally.
How it happened is beyond me.

Is there a quick script that I could write to get rid of these?
0
Comment
Question by:breeze351
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 9

Expert Comment

by:bas2754
ID: 41763851
I ran into this once and followed the guidance of this article to resolve:
-----
https://support.microsoft.com/en-us/kb/139444

I would advise testing on a test table first and ensure the results are what you expect.  Another expert may be able to provide exact commands for your situation, but this should get you headed in the right direction.
1
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41763865
Let me know how this grabs ya:  SQL Server Delete Duplicate Rows Solutions.

Applied to your example and tested on my SQL 2012 box...
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
	DROP TABLE #tmp
GO

CREATE TABLE #tmp (STOREKEY varchar(20), STORENUM int, BNMB int, STRT varchar(20))
GO

INSERT INTO #tmp (STOREKEY, STORENUM, BNMB, STRT) 
VALUES 
   ('NY1S05000010', 1001, 10, 'Spring Street'), 
   ('NY1S05000010', 1001, 10, 'Spring Street'), -- a duplicate row
   ('TX1S05000010', 5963, 10, 'Main Street')  -- a row I just made up

-- Before removing the duplicates
SELECT * FROM #tmp

-- Delete the duplicates
;with cte as (
SELECT 
	STOREKEY, STORENUM, BNMB, STRT, 
	row_number() OVER (PARTITION BY STOREKEY, STORENUM, BNMB, STRT ORDER BY (SELECT NULL)) as row_number 
FROM #tmp) 
DELETE 
FROM cte
WHERE row_number > 1

-- After removing the duplicates
SELECT * FROM #tmp

 

Open in new window

3
 

Accepted Solution

by:
breeze351 earned 0 total points
ID: 41763880
Ok, I thought there might an easier way to do it.
I just write a record to a new table, if the key is there, I don't write.  When I'm done I no longer have dupes.
Thanks
Glenn
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:breeze351
ID: 41763901
I've requested that this question be closed as follows:

Accepted answer: 0 points for breeze351's comment #a41763880

for the following reason:

I had already thought of it.  Thought there might be an easier way
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41763882
>Ok, I thought there might an easier way to do it.
Once the records are in the table, that's as easy as it gets.

>I just write a record to a new table, if the key is there, I don't write.
To prevent this from happening before the write you can create a unique index on the four columns, but there are other considerations in play such as memory, speed of insert/update, graceful way to handle violations, etc. that may not make this practical.

Another possibility is a trigger on the table to check inserts and updates, but if you're not comfortable with these then I'd recommend against them as it's added overhead.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 41764353
>> How it happened is beyond me.

A couple of possibilities :
1) There is a unique identifier as a key
2) There is no unique index
3) unlikely, but possible that an ALTER TABLE with NOCHECK has happened at some stage to disable constraints

Looking at the data, it would appear that storekey should be enough to make that the unique / primary key.

So, once you clean up the duplicates, it would be worthwhile to then create a unique index :
create unique index PK_STOREKEY on <your table name> (STOREKEY)

Open in new window

or if STOREKEY is not nullable (if it is, the above code works), create a primary key
alter table <your table name> with nocheck add constraint PK_STOREKEY PRIMARY KEY (STOREKEY)

Open in new window


Hope that helps a bit more with ways to avoid it happening again.
1

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question