Question asked by visitor Joe
Hi all! Good Evening.
I would like to know if it is possible to delete duplicate records from a table with out using "rowid" using MYSQL only. Can use "rownum". Plz help me in this regard.
Thanx in advance.
Bye
Question asked by visitor Joe
Hi all! Good Evening.
I would like to know if it is possible to delete duplicate records from a table with out using "rowid" using MYSQL only. Can use "rownum". Plz help me in this regard.
Thanx in advance.
Bye
I think you can't do that. Because Rownum is not linked with any record.
One example is here
create table t1(col1 int, col2 int, col3 char(50))
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 2, 'data value
SELECT col1, col2, count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1
delete from t1
where col1=1 and col2=1
Jaiprakash,
we need to delete only one of those duplicated rows.
but that delete statement will deletes both.
Yes, it deletes both 1st and 2nd records.
I think there is no solution without using ROWID.
Lack of WILL POWER has caused more failure than
lack of INTELLIGENCE or ABILITY.
-sutnarcha-
Try this....
delete from table where rowid=(select max(rowid) from table group by dup_field_name having count(*) > 2)
Thanks...
No, that is not possible in oracle..
hi
use rank() function to delete these duplicate records in oracle
I think this is not possible in Oracle.
You can try using analytical function to delete duplciates in oracle
Yes it is possible using analytic functions...
I think this one will help you...
select *
from ( select a.*,
row_number() over ( partition by column1 order by column2) r
from table a)
where r >1;
delete from table where (c1,c2,...) in
(Select * from table);
Yeah, It's possible in SQL Server 2005
try this...
This query delete duplicate records in one shot....
delete from Table_Name where Column_Name in
(
select Column_Name from Table_Name
group by Column_Name
having (count(Column_Name)>1)
)
Yeah, It's possible in SQL Server 2005
try this...
This query delete duplicate records in one shot....
delete from Table_Name where Column_Name in
(
select Column_Name from Table_Name
group by Column_Name
having (count(Column_Name)>1)
)
Try this
delete from employees emp1 where rowid < (select max(rowid) from employees emp2 where emp1.employee_id=emp2.employee_id);
@Harishkshetty,
The question is without using a rowid.