Prepare for your Next Interview

• GeekInterview
• Knowledge base
• Interview Questions
• Tech FAQs

Delete duplicate records without using "rowid"

This is a discussion on Delete duplicate records without using "rowid" within the SQL forums, part of the Databases category; 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 ...

Geeks Talk > Databases > SQL Delete duplicate records without using "rowid"

User Name Remember Me? Password

Register

FAQ

Members List

Calendar

Mark Forums Read

Thread Tools

Display Modes

#1 (permalink)   04-17-2007

Geek_Guest Expert Member   Join Date: Feb 2007 Posts: 1,279 Thanks: 0 Thanked 164 Times in 138 Posts

Delete duplicate records without using "rowid" 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

Sponsored Links

#2 (permalink)   04-18-2007

jamesravid Expert Member   Join Date: Jun 2006 Location: India Posts: 411 Thanks: 15 Thanked 33 Times in 25 Posts

Re: Delete duplicate records without using "rowid" I think you can't do that. Because Rownum is not linked with any record. __________________ Cheers, :) James:)

#3 (permalink)   04-23-2007

jbanx Junior Member   Join Date: Feb 2007 Location: Bangalore Posts: 17 Thanks: 0 Thanked 1 Time in 1 Post

Re: Delete duplicate records without using "rowid" 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

#4 (permalink)   04-23-2007

Barbie Expert Member   Join Date: Dec 2006 Location: Chennai Posts: 197 Thanks: 2 Thanked 16 Times in 14 Posts

Re: Delete duplicate records without using "rowid" Jaiprakash, we need to delete only one of those duplicated rows. but that delete statement will deletes both.

#5 (permalink)   04-23-2007

sutnarcha Expert Member   Join Date: Nov 2006 Location: Hyd-IND Posts: 523 Thanks: 1 Thanked 55 Times in 46 Posts

Re: Delete duplicate records without using "rowid" 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-

#6 (permalink)   07-11-2007

vadaliraghu Junior Member   Join Date: Jul 2007 Location: Chennai Posts: 1 Thanks: 0 Thanked 0 Times in 0 Posts

Re: Delete duplicate records without using "rowid" Try this.... delete from table where rowid=(select max(rowid) from table group by dup_field_name having count(*) > 2) Thanks...

#7 (permalink)   07-12-2007

debasisdas Moderator   Join Date: Jun 2007 Location: Bangalore,India Posts: 1,432 Thanks: 8 Thanked 125 Times in 112 Posts

Re: Delete duplicate records without using "rowid" No, that is not possible in oracle..

#8 (permalink)   07-12-2007

bhaski Contributing Member   Join Date: Apr 2006 Location: kolkata(now in noida) Posts: 56 Thanks: 9 Thanked 3 Times in 2 Posts

Re: Delete duplicate records without using "rowid" hi use rank() function to delete these duplicate records in oracle

#9 (permalink)   3 Weeks Ago

sureshkumar.mtech Junior Member   Join Date: May 2008 Location: Chennai Posts: 28 Thanks: 5 Thanked 0 Times in 0 Posts

Re: Delete duplicate records without using "rowid" Quote: Originally Posted by bhaski hi use rank() function to delete these duplicate records in oracle could you please explain with example query................. Thanks

Geeks Talk > Databases > SQL

« How to delete duplicate rows in a table | Need help with the query »

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

All times are GMT -4. The time now is 12:29 AM.