Re: query needed
- From: Tony van Lingen <tony_vanlingen@xxxxxxxxxxxxxxxxxxxxx>
- To: mhdmehraj@xxxxxxxxx
- Date: Mon, 18 May 2009 16:19:51 +1000
Mohammed,
I once wrote a script for this, but it needs a primary key to work.
You'd have to create an index and a primary key on that, which the
script then can use. You also need to create the exceptions table (a
standard Oracle table). The duplicate rows are listed in the exceptions
table when you issue an "alter table ...enable validate constraint ...
exceptions into exceptions" command, and this script uses that feature
to arbitrarily delete all duplicate rows. This is OK if the non-key
columns are also duplicates, but not if the rows are different, just
happen to have the same primary/unique key..
The script can be found at:
http://users.tpg.com.au/lingent/proj/util/oracle/rmdup.sql . The
exceptions table is created with the script UTLEXCPT.SQL, provided by
Oracle (it's in your $oracle_home directory).
You'll probably want to check and edit it before you use it, and do not
use it on a production database without thourough testing, as always!
If the assumtion that the rows are complete duplicates is not valid,
then you can still use the exceptions table, but you'll have to compare
the duplicate rows manually and correct them as required. The use of it
is explained in the SQL Reference guide, under the 'ALTER TABLE' command.
Hope this helps,
Cheers,
Tony
Mohammed Mehraj hussain wrote:
Hi Everybody,
I have a table with this columns
For Eg:
Table Name: emp
eno
ename
salary
date
-----------------------------
In eno i dont have primary key as well as unique index ,
so i got duplicate records in my table ,
so i need to check the table with the eno column , If any eno is
repeted then i need to delete one of those eno.
so , Plz provide a query to check the above case.
Rehgards,
Mohammed Mehraj Hussain
--
//www.freelists.org/webpage/oracle-l
Other related posts:
