April 23, 2008 at 11:56 pm
Hi All
Anyone can help me how to delete a duplication records retain one value in one table
Can anyone give a script for deleting duplicates records.
Please help me, I'm a new in SQL
Thank you very
April 24, 2008 at 5:52 am
Googling 'SQL server delete duplicate records' brought up a bunch of articles, including http://support.microsoft.com/kb/139444. Search engines are your friends.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
April 25, 2008 at 11:03 am
Do you have a primary key on your table. If you do have a primary key, then the solution should be easy.
Prasad Bhogadi
www.inforaise.com
April 25, 2008 at 5:29 pm
Ariel Dimapilis (4/23/2008)
Hi AllAnyone can help me how to delete a duplication records retain one value in one table
Can anyone give a script for deleting duplicates records.
Please help me, I'm a new in SQL
Thank you very
Ariel,
Can you post the CREATE TABLE statement for the table in question, please. Also, please identify the primary key if it has one and which columns you use to determine if rows have dupes or not... thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 25, 2008 at 8:29 pm
May try these steps:
1. Select the duplicated records into a temporary table
Select * into mytable from yourtable where clause …
2. Delete both records (duplicated records) from yourtable
3. Insert 1 record back to yourtable from mytable
Need to have primary key or unique constraint to prevent this happening. If you do have PK or unique constraint, should find out why this happened.
Or you can copy the whole table if it is OK to do so and test the above on the copy of the table first.
April 29, 2008 at 9:45 am
You cannot delete duplicates directly from a table unless you would like to delete rows, which are duplicated.
Instead, you can modify your table design, such as add a new column with unique value, such as IDENTITY.
Then, you can delete your duplicates.
After deleting duplicates, you can drop the newly created column.
April 29, 2008 at 6:20 pm
SQL ORACLE (4/29/2008)
You cannot delete duplicates directly from a table unless you would like to delete rows, which are duplicated.
Any bets? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
April 29, 2008 at 6:33 pm
You can try this
Select DISTINCT * into #table from table
GO
truncate table
GO
insert into table select *from #table
May 22, 2008 at 5:50 pm
here is a perfect solution for u.....
just create a temporary table
then move your duplicate record to the temporary using group by clause so only unique instance will be there,
then remove duplicate records from your table by taking id from the temporary table.
Once u will be delete your records , just insert the unique instance from the temporary table to ur table and u wiil be done. enjoy.....!
May 23, 2008 at 7:19 am
Jeff Moden (4/29/2008)
SQL ORACLE (4/29/2008)
You cannot delete duplicates directly from a table unless you would like to delete rows, which are duplicated.Any bets? 😉
Jeff, I am not doubting your ability - meaning no bet, I've seen lots of your scripts! - but I am curious to see your solution. I try and analyze most of the code you post (and others as well) and fit what I can into my skillset. Some of you on this site never cease to amaze me. Thanks.
-- You can't be late until you show up.
May 23, 2008 at 7:35 am
Do not know if this will be completely applicable to your problem but here is a technique (found on another SSC forum and being repeated by me) to delete duplicates, which might help you to come up with a solution.
Demo table:
CREATE TABLE [dbo].[PartDemo](
[ProductID] [int] NULL,
[CustomerId] [int] NULL,
[OrderAmount] [int] NULL,
[OrderDate] [datetime] NULL,
[Keyvalue] [varchar](2) NULL
) ON [PRIMARY]
GO
The data:
20101155/1/2008 12:00:00 AM
20102555/2/2008 12:00:00 AM
10 1 33 5/3/2008 12:00:00 AM
5 3 4 5/4/2008 12:00:00 AM
The procedure:
;with numbered as(SELECT rowno=row_number() over
(partition by PRODUCTID order by PRODUCTID),PRODUCTID,keyvalue from PartDemo)
delete from numbered where rowno>1
This will delete the row containing ProductId = 20, OrderAmount=55
Note In this example if there were many duplicates (Productid=20) all but one would be deleted by executing this procedure once.
May 23, 2008 at 8:06 am
thought I would expand on my previous reply, and demonstrate how to view what would happen to insure that a deletion using the code is truly what you want .... anyway added some additional data and then executed:
;with numbered as(SELECT rowno=row_number() over
(partition by PRODUCTID, Customerid order by PRODUCTID),PRODUCTID,CustomerId from PartDemo)
select * from numbered
This gave me:
rowno PRODUCTID CustomerId
120 101
220 101
320 101
420 101
120 102
220 102
320 102
420 102
520 102
Thus extending my check of "duplication" to the values in more than a single column of the table
May 23, 2008 at 9:00 am
tosscrosby (5/23/2008)
Jeff Moden (4/29/2008)
SQL ORACLE (4/29/2008)
You cannot delete duplicates directly from a table unless you would like to delete rows, which are duplicated.Any bets? 😉
Jeff, I am not doubting your ability - meaning no bet, I've seen lots of your scripts! - but I am curious to see your solution. I try and analyze most of the code you post (and others as well) and fit what I can into my skillset. Some of you on this site never cease to amaze me. Thanks.
Sorry, Terry... I was being a bit of a smart a$$ on that response (must've been in a foul mood from work) and I apologize for that short response. I'll crank out an example tonight...
--Jeff Moden
Change is inevitable... Change for the better is not.
May 23, 2008 at 9:23 am
Not a problem. I thought maybe you were calling SQL ORACLE out for a challenge! I don't have any issues here with duplicates. I just thought if you already had something, I'd like to see it. Like I said, all the different exposures help me in the long run.
-- You can't be late until you show up.
May 23, 2008 at 9:47 am
hang on a min... this looks familiar!
would this help:
[Delete Duplicates]
http://www.sqlservercentral.com/questions/T-SQL/62798/
🙂
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply