Oracle deletes a table with a large volume of duplicate data and the table has a primary key being referenced.

Source: Internet
Author: User

When oracle deletes a table with a large amount of duplicate data and the table has a primary key referenced, clear the duplicate data. 1. Save the data to be retained and put it into the temporary table USERCOMPANYINFO_tmp.

Java code create table USERCOMPANYINFO_tmp as (select * from USERCOMPANYINFO where rowid not in (select. rowid from usercompanyinfo a, (select max (. ROWID) RROWID, company from usercompanyinfo a group by. company having count (*)> 3) B WHERE. COMPANY = B. company and. ROWID <> B. RROWID ))

 

2 when the primary key in the table is referenced by other tables, you need to execute the following statement otherwise the error will be reported: Java code ORA-02266: unique/primary keys in table referenced by enabled foreign keys ORA-02266: unique/primary key-enabled external keyword reference in the table
Java code alter table USERCOMPANYINFO disable primary key cascade; // clear external references

 

3
Truncate table USERCOMPANYINFO; // clear data

 

4
Insert into USERCOMPANYINFO select * from USERCOMPANYINFO_tmp; // import data from the temporary Jiang table to the target data table

 

5
Alter table USERCOMPANYINFO enable primary key; // Add a primary key constraint to the target data table

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.