November 26, 2009 at 2:22 am
how to delete the duplicate records from a table?
like Item is a table and its columns are Item(IID,Iname,Price)
how to?
November 26, 2009 at 2:26 am
If the records are completely identical, and you wish to be left with just one of each, try this:
delete T1
from MyTable T1, MyTable T2
where T1.dupField = T2.dupField
and T1.uniqueField > T2.uniqueField
Is this what you are looking for?
November 26, 2009 at 4:36 am
no i didnt understand your script can you you explain it?
i want to delete duplicate records.
e.g:
IID Iname Price
1alo 34
2dhi 48
3banana 68
3banana 68
4apple 50
how to remove these records?
3banana 68
3banana 68
and if IID was a primary key?
3banana 68
4banana 68
then how to solve it?
November 26, 2009 at 4:45 am
Do you want BOTH Banana records deleted, or just one?
I will assume that you wish to keep one.
This script will only work if ID was a primary key.
delete T1
from MyTable T1, MyTable T2
where T1.lname = T2.lname
and T1.Price = T2.Price
and T1.ID > T2.ID
If there is no primary key, this would not work. I will have to give it more thought.
November 26, 2009 at 4:51 am
No just one record
3 banana 68
November 26, 2009 at 4:55 am
Ok.. then if ID is a primary key, then the script will work, leaving you one record for Banana.
Just change "MyTable" to your table name.
November 26, 2009 at 5:46 am
its not work , i try so many times.
November 26, 2009 at 5:47 am
Can you tell me your table name, and give me some more examples.
Also, can you tell me what happens when you execute the script?
November 26, 2009 at 5:53 am
2005 version:
Create table #t(IID int, INAME varchar(10), PRICE int)
delete from #t
INSERT INTO #t VALUES (1, 'alo', 34)
INSERT INTO #t VALUES (2, 'dhi', 48)
INSERT INTO #t VALUES (3, 'banana', 68)
INSERT INTO #t VALUES (3, 'banana', 68)
INSERT INTO #t VALUES (4, 'banana', 68)
INSERT INTO #t VALUES (4, 'apple', 50)
Select * from #t
DELETE t
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY INAME, PRICE ORDER BY IID) as row_num
FROM #t) t
Where row_num > 1
Select * from #t
You would attract more response if you give test data like this. Thanks.
---------------------------------------------------------------------------------
November 27, 2009 at 1:52 am
Nice work Nabha,
That was my first attempt at helping someone...
Your attempt was somewhat more sophisticated though.
It would be nice to know whether it has solved the problem or not..
🙂
<Edited: Excessive use of the word 'though'!>
November 27, 2009 at 9:08 am
using ROW_NUMBER is the classic way of finding/deleting dupes. Most do it with a CTE as the driver, which can be a bit easier for some to grok.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 27, 2009 at 9:28 am
Paul_Harvey (11/27/2009)
Nice work Nabha,That was my first attempt at helping someone...
Your attempt was somewhat more sophisticated though.
It would be nice to know whether it has solved the problem or not..
🙂
<Edited: Excessive use of the word 'though'!>
Thanks. Yours is no problem except it works only if there is a unique key identifying the duplicates. This is SQL Server 2005 concept called 'Windowing function'. Worth reading and practicing. Very useful thing!
---------------------------------------------------------------------------------
November 27, 2009 at 9:36 am
TheSQLGuru (11/27/2009)
using ROW_NUMBER is the classic way of finding/deleting dupes. Most do it with a CTE as the driver, which can be a bit easier for some to grok.
True. CTE version that SQLGuru has suggested for this case.
;With CTE(IID, IName, PRICE, Row_num) as
(SELECT *, ROW_NUMBER() OVER (PARTITION BY INAME, PRICE ORDER BY IID) as row_num
FROM #t)
Delete from CTE
Where row_num > 1
---------------------------------------------------------------------------------
November 27, 2009 at 4:45 pm
I'm no sql genius or anything, but is anyone concerned that the poster apparently has 2+ versions of the truth running around in his or her database?
Besides the technical detail of how to delete rows, shouldn't attention be placed on how multiple versions of the truth appeared in the first place, and which one is correct? (And then questions like setting keys properly, adequate normalization, etc. get asked?)
November 27, 2009 at 6:05 pm
sherifffruitfly (11/27/2009)
I'm no sql genius or anything, but is anyone concerned that the poster apparently has 2+ versions of the truth running around in his or her database?Besides the technical detail of how to delete rows, shouldn't attention be placed on how multiple versions of the truth appeared in the first place, and which one is correct? (And then questions like setting keys properly, adequate normalization, etc. get asked?)
1) the OP didn't ask for advice on how/why these duplicates got in, or how to keep them from appearing in the future. Attention should be paid first and foremost to answering the question possed by the OP.
2) I don't see that there are multiple versions of the truth here. The rows are duplicates, thus there is just one version of the truth.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply