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

[QUOTE=venkat_1984;11395]To delete duplicate record we use "distinct clause" select distinct(columname) from tablename.[/QUOTE]

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

[quote=raghvendra_rai;10824]How can delete duplicate value from a table

Raghvendra rai[/quote]

[SIZE=3][FONT=Times New Roman]Hi Friend,[/FONT][/SIZE]

[SIZE=3][FONT=Times New Roman]In order to delete the Duplicate records from a table ---[/FONT][/SIZE]

[COLOR=red][SIZE=3][FONT=Times New Roman]DELETE from <tablename> t1[/FONT][/SIZE][/COLOR]
[COLOR=red][SIZE=3][FONT=Times New Roman]where rowid not in ( select max(rowid) [/FONT][/SIZE][/COLOR]
[COLOR=red][SIZE=3][FONT=Times New Roman]from <tablename> t2 [/FONT][/SIZE][/COLOR]
[COLOR=red][SIZE=3][FONT=Times New Roman]where t1.col1 = t2.col1 [/FONT][/SIZE][/COLOR]
[COLOR=red][SIZE=3][FONT=Times New Roman]and t1.col2 = t2.col2 [/FONT][/SIZE][/COLOR]
[COLOR=red][SIZE=3][FONT=Times New Roman]and t1.col3 = t2.col3) [/FONT][/SIZE][/COLOR]

[FONT=Times New Roman][SIZE=3]Here [B]t1[/B] and [B]t2[/B] are one and the same. Just we are getting the records from the same table by using [B][I]sub-query[/I][/B].[/SIZE][/FONT]

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.

[QUOTE=jamesravid;12435]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.[/QUOTE]

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.

[QUOTE=jamesravid;12435]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.[/QUOTE]



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.

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