Parse the SQL statement used by Oracle to query and delete jobs

Source: Internet
Author: User
This article provides a detailed analysis of the implementation of Oracle SQL statements for querying and deleting jobs, for more information about how to query and delete duplicate records, see SQL statement 1. For more information about duplicate records in a table, see select * frompeoplewherepeopleIdin (selectpeopleIdfr ).

This article provides a detailed analysis of the implementation of Oracle SQL statements for querying and deleting jobs, for more information about how to query and delete duplicate records, see SQL statement 1. Find redundant duplicate records in the Table. duplicate records are based on a single field (peopleId) to determine the select * from people where peopleId in (select peopleId fr

This article provides a detailed analysis of the implementation of Oracle SQL statements for querying and deleting jobs. For more information, see

SQL statement for querying and deleting duplicate records
1. Search for redundant duplicate records in the Table. duplicate records are determined based on a single field (peopleId ).
Select * from people
Where peopleId in (select peopleId from people group by peopleId having count (peopleId)> 1)

2. Delete unnecessary duplicate records in the Table. Repeat records are determined based on a single field (eagleid), leaving only the records with the smallest rowid
Delete from people
Where peopleId in (select peopleId from people group by peopleId having count (peopleId)> 1)
And rowid not in (select min (rowid) from people group by peopleId having count (peopleId)> 1)
Note: rowid is not required for oracle .....
3. Search for redundant duplicate records in the table (multiple fields)
Select * from vitae
Where (a. peopleId, a. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1)

4. Delete redundant record (multiple fields) in the table, leaving only the records with the smallest rowid
Delete from vitae
Where (a. peopleId, a. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1)
And rowid not in (select min (rowid) from vitae group by peopleId, seq having count (*)> 1)
5. Search for redundant duplicate records (multiple fields) in the table, excluding records with the smallest rowid
Select * from vitae
Where (a. peopleId, a. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1)
And rowid not in (select min (rowid) from vitae group by peopleId, seq having count (*)> 1)
(2)
For example
There is A field "name" in Table ",
The "name" value may be the same for different records,
Now, you need to query items with duplicate "name" values between records in the table;
Select Name, Count (*) from A Group By Name Having Count (*)> 1
If the gender is also the same, the statement is as follows:
Select Name, sex, Count (*) from A Group By Name, sex Having Count (*)> 1
(3)
Method 1
Declare @ max integer, @ id integer
Declare cur_rows cursor local for select Main field, count (*) from table name group by main field having count (*)>; 1
Open cur_rows
Fetch cur_rows into @ id, @ max
While @ fetch ......

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.