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

In view of how to delete duplicate lines in SQLServe, this article introduces in detail the corresponding analysis and solutions, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

The Microsoft SQL Server table should not contain duplicate rows and non-unique primary keys. For brevity, we sometimes call the primary key "key" or "competition" in this article, but this always means "primary key". Repeated competition violates physical integrity and is not allowed in relational systems. SQL Server has various mechanisms to enforce entity integrity, including indexes, unique constraints, primary key constraints, and triggers. In some cases, however, duplicate primary keys may occur; if that happens, the duplicate primary key must be cleared. One of the situations where duplicate primary keys occur is when there is duplicate competition in non-relational data outside the SQL Server, and competitive uniqueness is not enforced when the data is imported. Another situation in which duplicate primary keys occur comes from database design errors, such as the failure to enforce entity integrity on each table. Duplicate competition is usually found when trying to create a unique index, because if a duplicate key is found, the creation of the unique index is aborted and the following message is displayed: Msg 1505, Level 16, State 1 Create unique index aborted on duplicate key. If you are using SQL Server 2000 or SQL Server 2005, you will receive the following error message: Msg 1505, Level 16, State 1 CREATE UNIQUE INDEX terminated because a duplicate key was found for object name'%. * ls' and index name'%. * ls'.The duplicate key value is% ls. This article discusses how to find and delete duplicate primary keys in a table. However, you should carefully examine processes that have duplicate keys to avoid repetition. For more information in this example, we will use the following table, which has duplicate competitive values. In this table, the primary key is two columns (col1, col2). We cannot create a unique index or primary key constraint because the two rows have duplicate competition. This procedure demonstrates how to identify and delete duplicate primary keys. The copy code is as follows: create table T1 (col1 int, col2 int, col3 char (50)) insert into T1 values (1,1, 'data value one') insert into T1 values (1,1,' data value one') insert into T1 values (1,2, 'data value two')

The first step is to identify which lines have duplicate primary key values: copy the code as follows: SELECT col1, col2, count (*) FROM T1 GROUP BY col1, col2 HAVING count (*) > 1

This returns a row for each set of duplicate competitive values in the table. The last column in this result is the number of duplicates for a specific competitive value. Col1 col2 1 1 2 if there are only a few sets of duplicate competitive values, the best way is to manually delete them one by one. For example, the copy code is as follows: set rowcount 1 delete from T1 where col1=1 and col2=1

The rowcount value should be the number of repeats of the given key value minus 1. In this example, there are two duplicate primary keys, so rowcount is set to 1. The col1/col2 value comes from the GROUP BY query result above. If the GROUP BY query returns multiple rows, the set rowcount query will have to run once for each of those rows. Each time you run the query, subtract 1 from the number of repeats that set rowcount to a specific competitive value. Before deleting a row, you should verify that the entire line is duplicated. Although full-line repetition is unlikely, it is possible that competition values are duplicated and the entire line is not duplicated. For example, a table with an ID number as the primary key has two different people (rows) with the same number, but each has a unique attribute. In this case, any problem that causes duplicate keys may also cause valid and unique data to be placed in the row. Before deleting the data, the data should be copied and saved for research and appropriate adjustments. If there are multiple sets of completely different duplicate competitive values in the table, it can be time-consuming to delete them one by one. In this case, the following methods can be used: 1. First, run the GROUP BY query above to determine how many sets of competing values are duplicated and the number of duplicates per group. two。 Select the duplicate key value to put in the temporary table. For example, the copy code is as follows: SELECT col1, col2, col3=count (*) INTO holdkey FROM T1 GROUP BY col1, col2 HAVING count (*) > 1

3. Select duplicate rows to put in the temporary table to clear duplicate values in the process. For example, the copy code is as follows: SELECT DISTINCT t1.* INTO holddups FROM T1, holdkey WHERE t1.col1 = holdkey.col1 AND t1.col2 = holdkey.col2

4. At this point, the holddups table should have a unique PK;, but this is not the case if T1 is unique with repeated contention (such as the SSN example above). Please verify that the keys in the holddups are unique and that there are no duplicate keys. If so, you must stop there to determine which rows you want to keep for a given duplicate key value. For example, the following query: copy code is as follows: SELECT col1, col2, count (*) FROM holddups GROUP BY col1, col2

Should return a count of 1 for each row. If the result is 1, proceed to step 5 below. If it is not 1, there is a situation where the key repeats and the row is unique, and you need to decide which rows to save. Typically, this will require discarding the row or creating a new and unique key value for the row. Perform one of these two steps for each such repeated contention in the holddups table. 5. Removes duplicate rows from the original table. For example, the copy code is as follows: DELETE T1 FROM T1, holdkey WHERE t1.col1 = holdkey.col1 AND t1.col2 = holdkey.col2

6. Put the unique row back into the original table. For example: INSERT T1 SELECT * FROM holddups

This is the answer to the question about how to delete duplicate lines in SQLServe. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

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.