SQL statements that collect duplicate data from MySQL and Oracle

SQL statement for data duplication between Oracle and MySQL

It can be said that this data is repeated, no matter in development, data maintenance and experience interviews, you should encounter common problems! Here, I also paid special attention to some articles on the Internet and collected them for your reference and study. It also facilitates your future review!

Repeated data processing in Oracle

How to query duplicate data

Select Field 1, Field 2, count (*) from table name group by field 1, Field 2 having count (*)> 1

PS: change the ">" number above to "=" to query non-duplicated data.

Oracle SQL statement for deleting duplicate data (delete all ):

Basic Structure for deleting duplicate data:

To delete the duplicate data, use the following statement.

Delete from table name a where field 1, Field 2 in

(Select Field 1, Field 2, count (*) from table name group by field 1, Field 2 having count (*)> 1)

Note: The preceding SQL statement is very simple, that is, to delete the queried data. However, the deletion execution efficiency is very low, and the database may be suspended for a large amount of data.

We recommend that you first Insert the queried duplicate data into a temporary table and then delete it. In this way, you do not need to perform another query When deleting the data. As follows:

Create table temporary table as (select Field 1, Field 2, count (*) from TABLE name group by field 1, Field 2 having count (*)> 1)

The above statement creates a temporary table and inserts the queried data into it.

You can perform the following deletion operations:

Delete from table name a where field 1, Field 2 in (select Field 1, Field 2 from temporary table );

Oracle SQL statement for deleting duplicate data (with a record left ):

In oracle, an automatic rowid is hidden, which gives each record a unique rowid. If we want to keep the latest record, we can use this field, retain the record with the largest rowid In the duplicate data.

Use ROWID to query duplicate data:

Select a. rowid, a. * from table name

Where a. rowid! =

(Select max (B. rowid) from table name B

Where a. Field 1 = B. Field 1 and

A. Field 2 = B. Field 2)

The SQL statement in parentheses queries the records with the largest rowid, while the external statement queries the repeated data except the largest rowid.

As a result, we need to delete the duplicate data and keep only the latest data record. Then we can write it like this:

Delete duplicate data (leave one of the largest rowids)

Delete from table name a where a. rowid! =

(Select max (B. rowid) from table name B

Where a. Field 1 = B. Field 1 and

A. Field 2 = B. Field 2)

Delete duplicate data (leave one of the smallest rowids)

Delete tab t where T. rowid> (

Select min (t2.rowid) from tab T2 where T. col2 = t2.col2 and T. col8 = t2.col8

)

Of course, the execution efficiency of the preceding statement is very low. You can consider creating a temporary table to determine the repeated fields and rowid to be inserted into the temporary table, and then compare them When deleting the table.

Create Table temporary table as select a. Field 1, A. Field 2, max (A. rowid) dataid from formal table a group by A. Field 1, A. Field 2;

Delete from table name

Where a. rowid! =

(Select B. dataid from temporary table B

Where a. Field 1 = B. Field 1 and

A. Field 2 = B. Field 2 );

Commit;

Delete completely Repeated Records

If the two rows in the table have identical records, you can use the following statement to obtain the records after deduplication:

Select distinct * from Table Name

You can place the queried records in a temporary table, delete the original table records, and export the data in the temporary table back to the original table. As follows:

Create Table temporary table as (select distinct * from Table Name );

Drop table formal table;

Insert into formal table (select * from temporary table );

Drop table temporary table;

If you want to delete the duplicate data of a table, you can create a temporary table first, import the data after the duplicate data is removed to the temporary table, and then import the data into the formal table from the temporary table, as follows:

Insert into t_table_bak

Select distinct * From t_table;

How to query and delete duplicate records in MySQL

Query and delete duplicate records (example)

1. Search for redundant duplicate records in the Table. duplicate records are determined based on a single field (peopleid ).

Select * from table where tableid in (select tableid from Table group by tableid having count (tableid)> 1)

2. Delete unnecessary duplicate records in the Table. Repeat records are determined based on a single field (tableid), leaving only the records with the smallest rowid.

Delete from table where tableid in (select tableid from Table group by tableid having count (tableid)> 1) and rowid not in (select Min (rowid) from table group by tableid having count (tableid)> 1)

3. Search for redundant duplicate records in the table (multiple fields)

Select * from vitae a where (a. tableId, a. seq) in (select tableId, seq from vitae group by tableId, seq having count (*)> 1)

4. Delete redundant record (multiple fields) in the table, leaving only the records with the smallest rowid

Delete from vitae a where (a. tableId, a. seq) in (select tableId, seq from vitae group by tableId, seq having count (*)> 1)

And rowid not in (select min (rowid) from vitae group by tableId, seq having count (*)> 1)

5. Search for redundant duplicate records (multiple fields) in the table, excluding records with the smallest rowid

Select * from vitae a where (. tableId,. seq) in (select tableId, seq from vitae group by tableId, seq having count (*)> 1) and rowid not in (select min (rowid) from vitae group by tableId, seq having count (*)> 1)

6. query a table. The values of a column are the same SQL statement:

For example

There is A field "name" in Table A, and the "name" values may be the same between 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