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

"distinct" is used to eliminate duplicate records for display purpose only,not to delete records from table.

__________________
--
Brijesh Jain

Hi Friend,

In order to delete the Duplicate records from a table ---

DELETE from <tablename> t1
where rowid not in ( select max(rowid)
from <tablename> t2
where t1.col1 = t2.col1
and t1.col2 = t2.col2
and t1.col3 = t2.col3)

Here t1 and t2 are one and the same. Just we are getting the records from the same table by using sub-query.

__________________
Sanghala
www.dwforum.net - A Great resource for Data Warehousing Professionals

If you are satisfied with my reply. Please click “Thanks” button which increases the reputation and can serve you better.

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 <table_name> where rowid not in(select min(rowid) from
<table_name> group by <all_primary_key_columns>);

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.

__________________
Cheers,
:) James:)

Rowid function is invalid in SQL Server.
Rowid is the best way to eliminate duplicate records in Oracle & in SQL Server, the distinct function can be used.

__________________
*** Innila ***

NO, we are not having rowid function in SQL server.

Therefore the above query will not applicable to delete the Duplicates from a table in SQL server.

__________________
Sanghala
www.dwforum.net - A Great resource for Data Warehousing Professionals

If you are satisfied with my reply. Please click “Thanks” button which increases the reputation and can serve you better.

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