Delete duplicate records without using "rowid"

Geek_Guest · #1 (**permalink**) 04-17-2007

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

jamesravid · #2 (**permalink**) 04-18-2007

I think you can't do that. Because Rownum is not linked with any record.

jbanx · #3 (**permalink**) 04-23-2007

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

Barbie · #4 (**permalink**) 04-23-2007

Jaiprakash,
we need to delete only one of those duplicated rows.
but that delete statement will deletes both.

sutnarcha · #5 (**permalink**) 04-23-2007

Yes, it deletes both 1st and 2nd records.

I think there is no solution without using ROWID.

vadaliraghu · #6 (**permalink**) 07-11-2007

Try this....
delete from table where rowid=(select max(rowid) from table group by dup_field_name having count(*) > 2)

Thanks...

debasisdas · #7 (**permalink**) 07-12-2007

No, that is not possible in oracle..

bhaski · #8 (**permalink**) 07-12-2007

hi
use rank() function to delete these duplicate records in oracle

sureshkumar.mtech · #9 (**permalink**) 11-06-2008

Quote:

Originally Posted by bhaski

hi
use rank() function to delete these duplicate records in oracle

could you please explain with example query.................

Thanks

nathsambu · #10 (**permalink**) 12-06-2008

I think this is not possible in Oracle.

amitpatel66 · #11 (**permalink**) 01-15-2009

You can try using analytical function to delete duplciates in oracle

ecearund · #12 (**permalink**) 01-16-2009

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);

asthutc · #13 (**permalink**) 01-16-2009

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)
)

asthutc · #14 (**permalink**) 01-16-2009

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)
)

harishkshetty · #15 (**permalink**) 01-22-2009

Try this

delete from employees emp1 where rowid < (select max(rowid) from employees emp2 where emp1.employee_id=emp2.employee_id);

amitpatel66 · #16 (**permalink**) 01-22-2009

@Harishkshetty,

The question is without using a rowid.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Filling "Date Creation" field with the current day	Lokesh M	Test Director	1	02-27-2008 08:39 AM
Table that houses "field selection"	Cayenne	SAP R/3	0	04-12-2007 02:02 PM
What is ""developing procedures and scripts (UNIX/Windows/Oracle)	spd15	Testing Issues	1	04-04-2007 09:01 AM
Business Objects - parsed failed "ORA-00936: missing expression :-936"	JobHelper	Data Warehousing	0	02-11-2007 06:06 AM
McAfee launches "TOTAL PROTECTION" Beta	Lokesh M	Geeks Lounge	0	06-22-2006 08:44 AM

The Following User Says Thank You to jbanx For This Useful Post:
nathsambu

Delete duplicate records without using "rowid"

Delete duplicate records without using "rowid"

Similar Threads