Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

How to delete duplicate entries in a table ?


  • Please log in to reply
12 replies to this topic

#1 evk_87

evk_87

    CC Lurker

  • Just Joined
  • Pip
  • 6 posts

Posted 07 September 2008 - 08:02 PM

In oracle 9i ........How to delete duplicate entries in a table ?

if suppose in a table named office, one or more entry(row) is repeated more then twice or minimum twice.
I need a query to delete the multiple entries ....

Note:
--->No constraints applied on the table.
--->No Primary Key
--->You cannot create any object....that is no view or a duplicate table can be created

Plz help !!!
  • 0

#2 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 08 September 2008 - 08:19 AM

You may need to delete all duplicates and then reinsert a single copy of the duplicated data.
  • 0

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#3 Orjan

Orjan

    CC Mentor

  • Moderator
  • 2918 posts
  • Location:Karlstad, Sweden
  • Programming Language:C, Java, C++, C#, PHP, JavaScript, Pascal
  • Learning:Java, C#

Posted 08 September 2008 - 09:24 AM

to read out all unique records, this should help

select distinct * from mytable


but that is just one small part of the problem. as he don't have any creation rights
I guess he can't create temporary tables either, if he could, this would be much easier

now, I guess the simplest way is to export the table contents to somewhere else
or at least the unique records, then delete everything and after everything import the unique records.

one more thig is, does the records have an auto increment field, then a lot might be so much different and difficult, as those id's might need to be preserved.
  • 0

#4 Mojo

Mojo

    CC Newcomer

  • Just Joined
  • PipPip
  • 11 posts

Posted 13 September 2008 - 10:15 PM

If you want to delete duplicate row's find any common column between them and delete it, for Example
DELETE FROM t1 WHERE c1='a common phrase between duplicate columns';

another than that i assume if your rows are being duplicated that ur not using a unique constraint or that your using an automatically generated unique column so try having a composite constraint as it might nullify the possibility of having duplicate rows. other than that dnt really know of any method that is easier to delete duplicate rows expect for manually deleting them.
  • 0

#5 Orjan

Orjan

    CC Mentor

  • Moderator
  • 2918 posts
  • Location:Karlstad, Sweden
  • Programming Language:C, Java, C++, C#, PHP, JavaScript, Pascal
  • Learning:Java, C#

Posted 14 September 2008 - 03:00 AM

Well, It's not that simple, what if different users enters the same info many times, unaware of each other into say a common address book? it's hard to prevent, and then you need to sort these out rather easy, withouot picking by hand etc.

it's dangerous to do a "delete from" on a live database like that when doing such hunting, especially when you have no create rights and can't backup information within the db. I suggest the export-cleaning-reimport version rather than working directly in the live db in this case, just for safety reasons.
  • 0

#6 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 14 September 2008 - 10:36 AM

Also, you really need to create some primary keys to prevent this sort of thing in the future. At the very least, use a GUID so you have SOMETHING distinct.
  • 0

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#7 Mojo

Mojo

    CC Newcomer

  • Just Joined
  • PipPip
  • 11 posts

Posted 14 September 2008 - 08:32 PM

A common address book can be repeated but things such as ID number should exist as a a primary key which cant be repeated, or a unique key, to prevent data from being duplicated, or a database trigger which disallows the same row to be repeated.
And dnt really think its a live database and even if it was, what good it is if it has duplicated records, if the data is not really important i suggest creating a new one with Constraints, if not there's no other why i know off expect to back the data and delete them using the For Update in Oracle
  • 0

#8 Orjan

Orjan

    CC Mentor

  • Moderator
  • 2918 posts
  • Location:Karlstad, Sweden
  • Programming Language:C, Java, C++, C#, PHP, JavaScript, Pascal
  • Learning:Java, C#

Posted 15 September 2008 - 03:53 AM

I can see that almost everyone has missed out a very special thing that the author of this thread stated:

Note:
--->No constraints applied on the table.
--->No Primary Key
--->You cannot create any object....that is no view or a duplicate table can be created


so, all your suggestion of rearranging the table is totally useless in this case, as it's not his system and has not much rights to the database

don't make your ideas so easily based as it isn't so easy as you tell as "just make sure there is a primary key" and similar, as he can't change the DB structure! Be a little bit innovative instead and figure out how you actually help this guy??
  • 0

#9 Mojo

Mojo

    CC Newcomer

  • Just Joined
  • PipPip
  • 11 posts

Posted 15 September 2008 - 05:25 AM

ok we'll try to be a bit more innovative lol

"SELECT DISTINCT * FROM YourTable"
Execute this query and then export the data on an excel sheet the data which is outputed after executing the query.

After exporting it Truncate the table and i believe that u dnt have 2 be an administrator to use this command.
"TRUNCATE TABLE yourTable" this is gna delete all of the rows in the table but well keep the structure as it is.
and then import what you exported to the truncated table.

in this way you'll be able to delete all of the duplicated data and replace them with the original.

Regards
  • 0

#10 Orjan

Orjan

    CC Mentor

  • Moderator
  • 2918 posts
  • Location:Karlstad, Sweden
  • Programming Language:C, Java, C++, C#, PHP, JavaScript, Pascal
  • Learning:Java, C#

Posted 15 September 2008 - 06:21 AM

and if you can't use truncte, you would still be able to use "delete from table;" which cleans the table, but doesn't reset any counters and stuff...
  • 0

#11 shokosugi

shokosugi

    CC Newcomer

  • Just Joined
  • PipPip
  • 10 posts

Posted 30 October 2008 - 09:35 PM

use DISTINCT
  • -2

#12 Orjan

Orjan

    CC Mentor

  • Moderator
  • 2918 posts
  • Location:Karlstad, Sweden
  • Programming Language:C, Java, C++, C#, PHP, JavaScript, Pascal
  • Learning:Java, C#

Posted 31 October 2008 - 02:50 AM

hmm.. it's not just that easy, if you delete with DISTINCT, in my world, you would have the posts that were to be deleted left...

by the way, why are you re-opening so many several month old issues like this and just bumping threads?? it's not what to call a good forum-netiquette... and double-posting like you never had a forum expericence in your life.. get real please...
  • 0




Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download