Thread: Delete duplicate record

How can delete duplicate value from a table

Raghvendra rai

DELETE FROM our_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM our_table
GROUP BY column1, column2, column3... ;

To delete duplicate record we use "distinct clause" select distinct(columname) from tablename.

--for ex.
--if there is a table... like..

Create table Xyz
(
x varchar(5),
y varchar(5)
);

--and you have inserted following row 3 or 4 times..
insert into xyz values('a','b')
---you can use following stored procedure...
create procedure removeduplicate
as
begin
Create table #Xyz
(
x varchar(5),
y varchar(5)
)
insert into #xyz select distinct * from xyz;
delete from xyz
insert into xyz select * from #xyz;
select * from xyz
end

Thanks for sending me this message. Tell me the syntax to create a user defined function.

you shall get the syntax in google.
I feel there is no need to post here for such things.

Delete from where rowid not in(select min(rowid) from
group by );

Do we have rowid function in Sql Server??

I think this is the right thread to ask this question since couple of you guys suggested to remove the duplicate records by using rowid function in sql server.

DELETE FROM tablename WHERE rowid not in(SELECT MIN(rowid)FROM tablename GROUP BY column1,column2 )

Rowid is not there in Sql Server 2000.
Rowid can be used in Oracle only.

Do not get confused.

We can delete the duplicate records by using having clause.

DELETE FROM table_name
WHERE ROWID NOT IN (SELECT max(ROWID)
FROM table _name
GROUP BY duplicate_values_field_name);

Row ID function not in Sqlserver 2000 but sql server 2005 row id function is there