Re: query needed

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
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l