By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,767 Members | 1,302 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,767 IT Pros & Developers. It's quick & easy.

removing duplicate records

P: 24
I'm working on removing duplicate records from a table in sql.
I've tried using select distinct, and I've grouped by but I'm still running into some issues. Attached is a sample of the output table.
Attached Files
File Type: txt example.txt (338 Bytes, 410 views)
Sep 28 '09 #1
Share this Question
Share on Google+
7 Replies

Expert 100+
P: 1,134
and the query looks like?

Also, when you say you are trying to remove duplicate records from a table...
do you mean

1)you want to actually delete the extra records from the table?
Or do you mean
2) you want a query that dosn't display the duplicates but leaves them in the table?

Reason I ask is because your question suggests to me that you want the first option but you are using a query that does the second option and then wondering why the duplicates are still in the table
Sep 29 '09 #2

P: 149
you mean duplicate columns are having same "Load Id" and "Shipment Id" values?

there is no other unique id to identify duplicates.. for delete operation..
Can you add an extra auto increment column into table, by which we can separate out duplicate records and delete them..
Sep 29 '09 #3

P: 24

Yes I would like to delete the extra records from the table, that's exactly what I'm looking to do. Here's the code I tried to use but it removes too much, I found it while browsing the internet, it was borken down into 4 parts and does not do what I really want it to do.

--part 1

select * from tblChargeAmts


with temptable as
select ROW_NUMBER() over (PARTITION BY [Load Id], [Ship Id] ORDER BY [Pc Code]) As rownumber,*
FROM tblChargeAmts
Select * FROM temptable

--part 3

with temptable as
select ROW_NUMBER() over (PARTITION BY [Load Id], [Ship Id] ORDER BY [Pc Code]) As rownumber,*
FROM tblChargeAmts
delete from temptable where rownumber > 1


select * from tblChargeAmts
Sep 29 '09 #4

Expert 100+
P: 1,134
Yes that query will completely remove all rows that are duplicated.
You don't want that, you want to leave just one of the records but remove the extra's, right.
You can't delete the duplicates manually because SQL Server won't let you.

There are many ways to achieve that.
1) add an auto number column to the table and either
write a query to delete each duplicate that has the highest auto number.

delete them manually

Once all duplicates are gone you can remove the new field

2) Create a copy of the table and delete all records from it(the copy).
Then do
Expand|Select|Wrap|Line Numbers
  1.     INSERT INTO TheCopyTable
  2.     SELECT distinct * from TheOriginalTable
Then you could delete TheOriginalTable and rename TheCopyTable
Delete all records in TheOriginalTable and reinsert them from the TheCopyTable and then delete TheCopyTable
Expand|Select|Wrap|Line Numbers
  1.     INSERT INTO TheCopyTable
  2.     SELECT distinct * from TheOriginalTable
  4.     DELETE FROM TheOriginalTable
  6.     INSERT INTO TheOriginalTable
  7.     SELECT * from TheCopyTable
  9.     DROP TABLE TheCopyTable
3) Similar to 2 but into a temporary table instead of a copy table
Then delete all records from TheOriginalTable and reinsert them back
from the temp table

4).... Use your imagination

5).... There are lots of possibilities

6) It all depends on your particular situation

If this is a one off task then I suggest that Option 2 may be easiest for you.

You really should make "Load Id" and "Shipment Id" key fields so that
it will be impossible for users to create duplicate records in the future.
Sep 29 '09 #5

P: 1

I have been trying to accomplish the same thing for a while, thanks. But, I import data from AS400 to SQL Server and my problem is importing duplicates and the fact that I have no PK in that table. I want to import only distinct values.


DJ Khalif
Oct 1 '09 #6

Expert 2.5K+
P: 2,878
You're going to have to import it first into a working table and run something to update your SQL Server main table.

Happy Coding!!!

--- CK
Oct 1 '09 #7

Expert 100+
P: 1,134
I also, routinely do migration of data from an ERP software called M3 (or MoveX) on an AS400 to SQLServer via DTS.

I presume you are using DTS to import your data from an AS400 into SQLServer.
If that is the case I don't really understand the issue?
Why wouldn't you just do a

SELECT DISTINCT in the DTS query that is selecting the data on the AS400.
That should stop the duplicates getting into the SQLServer Table in the first place!

It also raises some questions.
In the DTS the query that is selecting data from the AS400.
Are you joining tables?
If so, do you not have enough condions in one of the WHERE clauses,
and that is why you are getting duplicate records imported to SQL server.

In my posts, as with all experts here, I am just trying to be helpful.
Not knowing your experience level, I may say/ask something that you have tried/know already.
Oct 1 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.