Lets assume that you are using SQL Server 2005 for your current project. You found that you have few rows which have duplicate data in all the columns. Lets consider that you have table name “Example” which has two columns ID and Name.
CREATE TABLE [dbo].[Example]
(
[ID] [int] NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
ON [PRIMARY]
Fig – (1) Create Statement for Table
Lets assume that you have following data in Example table.
Fig – (2) Data in Example table.
You can see that first two and last three rows are duplicate rows. All the values in these rows are same. Here is the insert script, if you want to do this practically in your local database.
INSERT INTO [Example] ([ID],[Name]) VALUES (1,Chirag)
INSERT INTO [Example] ([ID],[Name]) VALUES (1,Chirag)
INSERT INTO [Example] ([ID],[Name]) VALUES (2,‘Shailesh’)
INSERT INTO [Example] ([ID],[Name]) VALUES (3,‘Dipak’)
INSERT INTO [Example] ([ID],[Name]) VALUES (4,‘Mihir’)
INSERT INTO [Example] ([ID],[Name]) VALUES (4,‘Mihir’)
INSERT INTO [Example] ([ID],[Name]) VALUES (4,‘Piyush’)
Fig – (2) Insert Script.
Now you want to delete duplicate rows in such a way that only one row will be exist after delete statement. First let me write the query which will give return all the duplicate rows from table.
SELECT
[ID],[NAME],COUNT([ID])
FROM
[Example]
GROUP BY
[ID],[NAME]
HAVING
COUNT([ID]) > 1
Fig – (3) Query to identify duplicate rows in table.
Here I have used COUNT([ID]) in select statement as ID is not null filed. You can use any column which is not NULL. If all the columns in your table allows NULL value than you can use COUNT(*). The Difference between COUNT(Column Name) and COUNT(*) is, if your column allows null value and in table you have 5 records with 2 null values in ColumnA. If you use COUNT(ColumnA) it will returns 3 and if you use COUNT(*) it will returns 5. So COUNT(Column Name) ignores NULL value. Lets get back to our query. I have used all the column in SELECT and GROUP BY clause. You also have to write all the columns of your table in SELECT and GROUP BY clause. This way you can identify all the duplicates row from table.
Lets assume that you have to delete the row which has value (1, ‘Chirag’) so that only one row remains. Here is the query, (Note: This will work only in SQL Sever 2005)
DELETE TOP(1) FROM [Example] WHERE [ID] = 1
Fig – (3) Delete single row from duplicate rows.
Here I have used TOP(1) , If you have n rows which has all the values same than you have to use TOP(n-1) so that only 1 row will be remain after delete statement. To delete all the duplicate rows you need to write a cursor as shown below,
DECLARE @ID int
DECLARE @NAME NVARCHAR(50)
DECLARE @COUNT int
DECLARE CUR_DELETE CURSOR FOR
SELECT [ID],[NAME],COUNT([ID]) FROM [Example] GROUP BY [ID],[NAME] HAVING COUNT([ID]) > 1
OPEN CUR_DELETE
FETCH NEXT FROM CUR_DELETE INTO @ID,@NAME,@COUNT
/* Loop through cursor for remaining ID */
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE TOP(@COUNT -1) FROM [Example] WHERE ID = @ID
FETCH NEXT FROM CUR_DELETE INTO @ID,@NAME,@COUNT
END
CLOSE CUR_DELETE
DEALLOCATE CUR_DELETE
Fig – (4) Cursor to delete all duplicate records
This is all about deleting duplicate rows in SQL Server 2005.
Now to do the same in SQL server 2000. There is function called ROWCOUNT in SQL. I have used same [Example] table. You can do this by,
SET ROWCOUNT 1
DELETE FROM [Example] WHERE [ID] = 1
Fig – (5) Delete duplicate row in SQL Server 2000
ROWCOUNT function specify that how many rows will be affected by the statement which is immediately written below. Here also you have to write ROWCOUNT (n -1) to delete n duplicate rows such that only 1 row will remain in database.
Happy Programming !!
This demonstrates good usage of RowCount.
However to delete duplicate row, I prefer following solution, which works with all version without any issues.
SQL SERVER – Delete Duplicate Records – Rows
Regards,
Pinal Dave (SQLAuthority.com)
Gr8 thing..
Also can be done by altering the table to create an identity column and then deleting the rows except 1.
Here is another solution for SQL Server 2005,
WITH
Test AS (SELECT ROW_NUMBER () OVER ( PARTITION BY ID, FNAME , LNAME ORDER BY ID) AS A FROM tbl1 ) DELETE FROM Test WHERE A > 1
this is very gud query for deleting the duplicate record…
thanx Dipak Patel
Pinal Dave,
How can I delete duplicate records? I like your site.
deleting duplicate rows : how to perform in oracle ?
HI Radhika,
If you want to delete duplicate rows in oracle
then here is your ans..
Delete from X where rowid not in ( Select max(rowid) from X
group by field name 1,2,3 havin count(*)>=1;
Hope so it will be work..
X– Table Name
1,2,3– Field name..
Tx and Regards
Amit (OCP)
amit.btext@gmail.com
CREATE TABLE Example
(
ID int NOT NULL,
Name varchar2(50)
)
INSERT INTO Example (ID,Name) VALUES (1,’Jeya’)
INSERT INTO Example (ID,Name) VALUES (1,’ Jeya ‘)
INSERT INTO Example (ID,Name) VALUES (2,’Kumar’)
INSERT INTO Example (ID,Name) VALUES (3,’Senthil’)
INSERT INTO Example (ID,Name) VALUES (4,’Ram’)
INSERT INTO Example (ID,Name) VALUES (4,’Ram’)
DELETE FROM EXAMPLE E1
WHERE E1.ROWID >
( SELECT MIN(T2.ROWID) FROM EXAMPLE E2
WHERE E1.ID = E2.ID
AND E1.ID = E2.ID );
Thank You
Hi wanna know how to count the null values present in a table in single statement.
Every one give count in coloum, but I need count for whole table
Amit,
I have no Idea. However I will try in this weekend and let you know if I found.
i have that exact issue but my ID’s are not unique just the names are so i cant do a groupby id so i dont have the id so i dont know which one to delete, it just deletes them both… any help??
this very good. Finally i got this query from you thanks
Thanks…it was very helpful…
No.. It ain’t helpful… The answers was a fool guess… good to know that its sql 2005 tho..
Thanks its woriking
Consider that My Table is having structure as follows
Col1 – ObjectName
Col2 – PriceValue
for Example my records are like
ObjectName PriceValue
—————————————-
A 20
A 50
B 10
B 30
C 50
How can I get the difference (Variance) between ObjectName.
I have tried and getting output with SUM Value using Group by clause as,
A 70
B 40
C 50
But i need the difference between each two row based on Group By ObjectName as.
A -30
B -20
C 50
Would you help me in solving this issue.
Thanks in Advance
Cheers,
🙂 raghav
Raghav,
Here is the ans of ur query. Let me know if I am wrong,
select RemainingSum.productname,(maxprice.price – RemainingSum.price) price
FROM
(
select productname,sum( price)-max( price) price from test
group by productname
) as RemainingSum
inner join
(
select productname,max( price) price from test where productname in(
select distinct productname from test) group by productname
) as maxprice
on RemainingSum.productname = maxprice.productname
waste
when i typed ‘delete rows in sql2005’ in google – I got your website. But your website looks pretty simple and dont find meta tag/data, did you run some SEO tools?
Just curious!
really your solution was very much helpful for me
thank
Hi all
i don’t think using cursors would be the good option to delete duplicate records.
this can be achieved easily by another query. i am saying this because it slower down the process when it comes to delete duplicate records in table contains 1000s of records.
Hi all
i don’t think using cursors would be the good option to delete duplicate records.
this can be achieved easily by another query. i am saying this because it slower down the process when it comes to delete duplicate records in table contains 1000s of records.
But thanks for this cocept. thank you
Can you tell hoe to delete top n records from the table
This really good solution and it was very useful for me very thank you.
can you tell me how to select the top second record in the table without using top2 command pls reply me as soon as you can
hello
I as using SQL Server 2000, and i have tried ur duplication row deleting cursor. But when I am using
set ROWCOUNT (@count-1)
DELETE FROM [Example] WHERE [ID] = 1
then it gives a error message
incorrect syntax near ‘(‘
when in used
set ROWCOUNT @count-1
DELETE FROM [Example] WHERE [ID] = 1
then it gives a error message
incorrect syntax near ‘-‘
but when i give a no intead of parameter then it works fine
can u help me regarding this problem
thanks
Thanks, it was very helpful for delete duplicate record
I Have the same table as Raghav’s see below
13 Raghav
Consider that My Table is having structure as follows
Col1 – ObjectName
Col2 – PriceValue
for Example my records are like
ObjectName PriceValue
—————————————-
A 20
A 50
B 10
B 30
C 50
I want to be left with the record with the lowest price and delete the rest.
any ideas
thanks
John.
Hii…
This is just to say thanks to you.
Some time i stuck on problem and then i always use your ideas and solution to solve it out.
If it is possible can u please forward me your all suggestion on any query into my email address i will greatly thank full to you.
Thanks chiragrdarji
yogesh
I have Table Like this
Id Name
1 ABC
2 XYZ
I want to display this record like this in sql 2005
tell me the query for disply record shown below.
Id 1 2
Name ABC XYZ
Thanks. Information was helpful……..
The best, simplest and cleanest solution for deleting duplicate rows when the table does not have unique identifiers.
Works like magic! Thanks a mullion guys.
Nice write-up. It was quite helpful to understnd the concept.
Please refer this very nice helpful article, commented by Vamshi
John Spencer,
Why not try a MIN (FIELD) function call to isolate the minimum value you would like to retain and then exclude that value from the delete statement?
天明
if want to give two primary keys to delete the row from the sql the how will be the code?
Use On delete Cascade
hi friends,
i want to use a comment box in my application which data type can i use for that.
i am using the varbinary .
is it correct or any other solution is there for that.
please send me a correct answer as soon as possible to my gmail id.
and another problem is there
how to upload and download the document files in asp .net using C# and sqlserver-2005 which datatype can i used for this??
Thanks & Regards
A.kranti Kumar
Hi,
I have only a 1 column in a table and i need it to break it into 2 columns
plz help me about doing it
Thanks in advance
Jayanth
Excellent solution for deleting duplicate rows, it helped me a ton in a keyword search application.
Thanks!
Good use of count.
I found simple method @
Delete Duplicate rows in Sql Server
Thanks, It works
Hi
i want to delete all 1 records from table, only want to retain 1 year old data. how to do this using cursor ?
Delete from X where rowid not in ( Select max(rowid) from X
group by field name 1,2,3 havin count(*)>=1;
Hope so it will be work..
X– Table Name
1,2,3– Field name..
Tx and Regards
Amit
amit.btext@gmail.com
Hi
sry make mistake in last comment so sending another as:
i want to delete all records from 1 table, only want to retain 1 year old data. how to do this using cursor ?
i have written query using cursor as below :
DECLARE @ID char(10)
DECLARE c1 CURSOR READ_ONLY
FOR
SELECT id
FROM Test_sprt_chat_log_archive
WHERE DATEDIFF(Day, logged, getdate() ) > ‘365’
OPEN c1
FETCH NEXT FROM c1
INTO @ID
WHILE (@@FETCH_STATUS = 0 )
BEGIN
print @ID
Delete from Test_sprt_chat_log_archive where id=@ID
FETCH NEXT FROM c1
INTO @ID
END
CLOSE c1
DEALLOCATE c1
but it it deleting only one record at a time.. there are 2 milion records in that table.. so is this is feasible query ? please suggest efficient way to do this.
please help!!
Thank u !
Not that I’m totally impressed, but this is more than I expected when I stumpled upon a link on Digg telling that the info is quite decent. Thanks.
DELETE TOP(1) FROM AlocateProgram WHERE program_id=1
ERROR IS
Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword ‘top’.
This syntax will work only for SQL Server 2005 or later version.
Delete from X where rowid not in ( Select max(rowid) from X
group by field name 1,2,3 havin count(*)>=1;
Note:
X– Table Name
1,2,3– Field name..
Tx and Regards
amit.btext@gmail.com
HI Radhika,
If you want to delete duplicate rows in oracle
then here is your ans..
Delete from X where rowid not in ( Select max(rowid) from X
group by field name 1,2,3 havin count(*)>=1;
Hope so it will be work..
X– Table Name
1,2,3– Field name..
Tx and Regards
Amit (OCP)
amit.btext@gmail.com
Hello Sir/mam,
How can i delete one row out of two in sql server where all columns are same.(e.g reg_id, name, marks are samein both rows)?
if i have two rows and all columns of rows r same and we want to delete one row how can it possible?
e.g.
reg_id,name,marks all data fields r same…….
what is the syntax for deleting a record from a table using stored procedure
eahgsdjatkjasbck ygtreytugd iytg iueyt6et iytiuyiy eiryiuryiov eh
rpeu898r6789 v\pur987634 vi878979[p4ub kuyiuyiu4yi
]
Hello,
Have you considered using this method. it is quick and easy but i works for sql 2005 and up.
;WITH X (Name, Ranking)
AS
(SELECT Name,Ranking = DENSE_RANK()
OVER(PARTITION BY Name ORDER BY NEWID() ASC)
FROM table_in_Question)
DELETE FROM X
WHERE Ranking > 1
this is assuming that the table does not have an id column.
the only problem is that i have to do this on a database that is on sql 2000, and it will not work
Hi Ankit Agarwal,
Please use the following code to delete the duplicate entries in SQL Server 2000.
Step 1 : Create Table
************************
create table test
(
id int,
name varchar(20)
)
Step 2 : Insert Values
************************
insert into test VALUES(1,’test’)
insert into test VALUES(2,’test’)
insert into test VALUES(2,’test’)
insert into test VALUES(3,’test’)
insert into test VALUES(4,’test’)
insert into test VALUES(5,’test’)
insert into test VALUES(6,’test’)
insert into test VALUES(7,’test’)
insert into test VALUES(7,’test’)
insert into test VALUES(7,’test’)
Step3 : Create Cursor
***************************
DECLARE @ID int
DECLARE @NAME NVARCHAR(50)
DECLARE @COUNT int
DECLARE CUR_DELETE CURSOR FOR
SELECT [ID],[NAME],COUNT([ID]) FROM [test] GROUP BY [ID],[NAME] HAVING COUNT([ID]) > 1
OPEN CUR_DELETE
FETCH NEXT FROM CUR_DELETE INTO @ID,@NAME,@COUNT
/* Loop through cursor for remaining ID */
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @COUNT = @COUNT -1
set rowcount @COUNT
DELETE FROM [test] WHERE ID = @ID
FETCH NEXT FROM CUR_DELETE INTO @ID,@NAME,@COUNT
END
CLOSE CUR_DELETE
DEALLOCATE CUR_DELETE
*********************************************
This code will definetly works.
Thanks,
d information was really helpful
Thank you soo much! Worked perfectly.
Delete top(1) from table where name = ‘blah’
Thanks!
gggggggggggggggggggggggggggggggggggggg
DELETE TOP(1) FROM [Example] WHERE [ID] = 1
not working in sql 2005
Thanks! This was what I was looking for. worked PERFECT
hello
i want insert date in a column
but dont want repetation
so please give me code for that using Sql Server 2005
I believe 6 line script can do a magic of deleting the duplicate records.
CREATE TABLE [dbo].[Temp](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[DOB] [date] NULL
)
Insert duplicate record where name and DOB are same and now run the below script to delete duplicate records. Use MIN(…) in case you don’t wish to delete first instance or use MAX(…) in case of last instance.
DELETE
— SELECT *
FROM Temp
WHERE ID NOT IN
(
SELECT MIN(ID)
FROM Temp
GROUP BY Name, DOB)
I tried it was very useful to delete duplicates.
Just to say thanks a lot! It saved me lots of time!
hello guys i have a problem anyone can tell me ??
i want to delete duplicate records i mean those ID who are existing more than 1 record and whose another column name which is called ses_date is null?? i mean i only want to delete the duplicate records which are having null in theri another column
Hi,
I want a help in sql server 2005 as mentioned below:
I want to select rows on top from a table which has more repeated values in a column.
For Example:
pEnt_ID ShortName FundID Received Date
—————————————————————
58731 M IX A-3 NULL NULL
58729 M IX A-1 4 NULL
58730 M IX A-2 4 NULL
58729 M IX A-1 23772 NULL
58730 M IX A-2 23772 09/08/2010
58729 M IX A-1 4 NULL
I want to display the rows which has maximum repeated value in pEnt_ID column ie. 58729 on top and then so on.
Like this:
pEnt_ID ShortName FundID Received Date
—————————————————————
58729 M IX A-1 4 NULL
58729 M IX A-1 4 NULL
58729 M IX A-1 23772 09/07/2010
58730 M IX A-2 4 NULL
58730 M IX A-2 23772 09/08/2010
58731 M IX A-3 NULL NULL
Please provide some query solution.
thanks for u r blogs ppl ..these helped me a lots
Dear Sir,
I am Doing a small Project Student Management Software in Windows Application
Front End : C# .Net 2008
Back End : Sql server 2008
In Server Daily BackUp are Placed Automatically. The Third Person was Restore the Backup they Alter The DataBase. So I Want The authentication to restore the DataBase Please Help sir
How to Keep one row from duplicate rows.
lets say I have 10 records in table and all of them are duplicating 3 times. so there will be 30 rows. now i want to delete 20 rows (duplicating) and keep alive other 10 rows (original). how is this possible?
Let me simplyfy my question:
1: How to Find Duplicate Rows from Table?
2: How to Delete only duplicated rows?
Example:
Table: Test
ID: Name:
1 Vishal
2 Vishal
3 Vishal
4 Website
How can I delete record 2 and 3 only?
Thanks
Hi,
I have a two tables,
Table A :
Col1 | Col2
========
A | Alphabet
B | Alphabet
C | Alphabet
D | Alphabet
Table B:
Col1 | Col2
=========
1 | No.
2 | No.
3 | No.
So now i want to display the result as
Col1 | Col2 | Col3 | Col4
=================
A Alphabet 1 No.
B Alphabet 2 No.
C Alphabet 3 No.
D Alphabet null null
Thank u
select t1.col1,t1.col2,t2.col1 as col3,t2.col2 as col4 from Table A as t1,Table B as t2
i want delete column in one table name…..
delete from table name where id =1
delete from p1 where id is null
here p1 is table name
working fine ,ThanQ
here is another one to delete duplicates
if table like dup[id , name]
create table #temp (rowid int,id int,Fname varchar(50),)
insert into #temp select ROW_NUMBER() over (order by id asc) as rowid,* from dup
delete from #temp where rowid not in (select max(rowid) from #temp group by id)
delete from dup
insert into dup select id,Fname from #temp
Hi,How to delete duplicate names without row id?
The best solution in my opinion is
DELETE FROM X
FROM (SELECT a1,
a2,
a3,
ROW_NUMBER() OVER (PARTITION BY a1, a2, a3 ORDER BY a1, a2, a3 ) RowNumber
FROM MyTable) X
WHERE X.RowNumber > 1
… given a1, a2, a3 are ALL the columns of the table.
Valid only in 2005+
i have that exact issue but my ID’s are not unique just the names are so i cant do a groupby id so i dont have the id so i dont know which one to delete, it just deletes them both… any help??
hi Nowshath
you can simply use common table expression (CTE).
in that you need to use Rank funcation
With t AS
(
Select , //in which u want to delete duplicate record.
RANK(ORDER BY PARTITION BY ) AS ‘Rank’
from
)
DELETE FROM t
WHERE t.Rank>1
Thanks 🙂