How can delete duplicate value from a table
Raghvendra rai
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
Last edited by jainbrijesh; 05-22-2007 at 07:29 AM.
Regards,
Brijesh Jain
---------------------------------------------------------
Connect with me on Skype: jainbrijesh
Google Plus : jainbrijeshji
Hi Friend,
In order to delete the Duplicate records from a table ---
DELETE fromt1
where rowid not in ( select max(rowid)
fromt2
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
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 fromwhere 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.
Sanghala
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