| SQL [message #374163] |
Tue, 29 May 2001 01:51  |
rinku
Messages: 4
Registered: December 2000
Location: India
|
Junior Member |
|
|
how will you delete duplicate records in SQL
ex:-
name age
rinku 20
ajit 21
rinku 20
ajit 21
the result should delete the duplicate file like as follows
name age
rinku 20
ajit 21
|
|
|
|
| Re: SQL [message #374175 is a reply to message #374163] |
Tue, 29 May 2001 08:25  |
Bala
Messages: 205
Registered: November 1999
|
Senior Member |
|
|
Hi,
in oracle you can use this
delete from t1 where t1.rowid <>
(select max(t2.rowid) from t2
where t1.name = t2.name
and t1.age = t2.age)
in sql server i think you can use identity
or you can use
set rowcount = 1
delete from t1 where name = 'rinku' and age = 20
but you have to do it for each duplicate set....
Bala.
|
|
|
|
| Re: SQL [message #374187 is a reply to message #374163] |
Tue, 29 May 2001 21:03 |
Rakesh Goel
Messages: 8
Registered: May 2001
|
Junior Member |
|
|
You can use the following command in any database environment
delete from tablename t1 where t1.rowid in
(select t2.rowid from tablename t2
group by name,age having count(*)>1)
as the following query returns only duplicate rows
select * from tablename t2 group by name,age having count(*)>1
if you have more columns in your table then in group by clause you have to use a combination which should have hold the unique values for a row.
Enjoy,
Rakesh
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
