12 replies to this topic

[evk_87]

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

[WingedPanther73]

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

[Orjan]

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.

[Mojo]

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.

[Orjan]

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.

[WingedPanther73]

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.

[Mojo]

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

[Orjan]

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

[Mojo]

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

[Orjan]

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

[shokosugi]

use DISTINCT

[Orjan]

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