Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to solve the complete repetition of database deletion and the duplication of some key fields". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations! I hope you can read it carefully and be able to achieve something!

1. The first repetition is easy to solve, and the method is similar in different database environments:

The following is the referenced content:

Mysql

Create table tmp select distinct * from tableName

Drop table tableName

Create table tableName select * from tmp

Drop table tmp

SQL Server

Select distinct * into # Tmp from tableName

Drop table tableName

Select * into tableName from # Tmp

Drop table # Tmp

Oracle

Create table tmp as select distinct * from tableName

Drop table tableName

Create table tableName as select * from tmp

Drop table tmp

This repetition occurs due to poor table design, which can be solved by adding a unique index column.

2. Such duplicate problems usually require the retention of the first record in the duplicate record, as follows. Suppose a duplicate field is Name,Address, and a unique result set of these two fields is required.

Mysql

The following is the referenced content:

Alter table tableName add autoID int auto_increment not null

Create table tmp select min (autoID) as autoID from tableName group by Name,Address

Create table tmp2 select tableName.* from tableName,tmp where tableName.autoID = tmp.autoID

Drop table tableName

Rename table tmp2 to tableName

SQL Server

Select identity (int,1,1) as autoID, * into # Tmp from tableName

Select min (autoID) as autoID into # Tmp2 from # Tmp group by Name,Address

Drop table tableName

Select * into tableName from # Tmp where autoID in (select autoID from # Tmp2)

Drop table # Tmp

Drop table # Tmp2

Oracle

DELETE FROM tableName T1 WHERE t1.ROWID > (SELECT MIN (t2.ROWID) FROM tableName T2 WHERE t2.Name = t1.Name and t2.Address = t1.Address)

Description:

1. The last select in MySQL and SQL Server gets the result set that Name,Address does not repeat (there is an extra autoID field, which can be omitted in the select clause when you actually write it)

two。 Because MySQL and SQL Server do not provide a rowid mechanism, row uniqueness needs to be achieved through an autoID column, and it is much more convenient to use Oracle's rowid processing. And using ROWID is the most efficient way to delete duplicate records.

This is the end of the content of "how to solve database deletion complete repetition and some key field duplication". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.