You are here:

Oracle/query for deleting rows

Advertisement


srinu wrote at 2012-11-14 12:00:59
select count(ACCOUNT_NO),ACCOUNT_NO from table_name

   group by ACCOUNT_NO

   having count(ACCOUNT_NO) > 1;





delete from table_name

  where (rowid, account_no)

   not in

    (select min(rowid), account_no from table_name  group by account_no);


Debabrat das wrote at 2015-08-19 20:28:59
By using the concept of patition embedded in  the row_number ()  function  it can come easy... just  use  the code

delete * from $table_name  where ( select  row_number() over (partition by $primary_key order by $primary_k) from  $table_name ) > 1;



For each partition the numbering starts fresh from 1 so if there is any duplicate for any of the primary key  it will be assigned a number greater than 1 and hence will be deleted.  


Oracle

All Answers


Answers by Expert:


Ask Experts

Volunteer


Peter Choi

Expertise

I am a senior Oracle DBA, PeopleSoft HCM specialist and Project Manager with 15+ years experience. I have been working with PeopleSoft (HRMS/HCM 5, 7, 7.x and 8.9, 9.x), Oracle RDBMS (7.3 - 11gR2) on various Unix and MS-Windows platforms, and some Oracle Application Server (9i - 11gR1). I also have experience with the configuration and administration of PeopleSoft's Internet Architecture (PIA) and the Oracle 11g Fusion Middleware and Oracle Business Intelligence (OBI).

©2016 About.com. All rights reserved.