Example of deleting duplicate records in a data table in SQL Server, SQL Server

Example of deleting duplicate records in a data table in SQL Server, SQL Server

[Project]

The users table in the database contains the u_name and u_pwd fields. The u_name contains duplicate items. Now we need to delete duplicate items!

[Analysis]

1. Generate a temporary table new_users with the same structure as the users table;
2. Create a cycle by id for the users table. Every time a record is read from the users table, check whether the same u_name exists in new_users. If not, insert it into the new table; if the same item already exists, ignore this record;
3. Change the users table to another name and change the new_users table to users.

[Program]
Copy codeThe Code is as follows:
Declare @ id int, @ u_name varchar (50), @ u_pwd varchar (50)
Set @ id = 1

While @ id <1000
Begin
If exists (select u_name from users where u_id = @ id)
Begin
Select @ u_name = u_name, @ u_pwd = u_pwd from users where u_id = @ id -- get source data
If not exists (select u_name from new_users where u_name = @ u_name) -- judge whether there are duplicate U-name items
Begin
Insert into new_users (u_name, u_pwd) values (@ u_name, @ u_pwd)
End
End
Set @ id = @ id + 1
End

Select * from new_users

[Method 2]
Assume that the Users table has the same name, and id is the primary key id field. Remove duplicate names in Users.

1. Store non-repeated IDs in a tmp1 table.
Copy codeThe Code is as follows: select min ([id]) as [id] into tmp1 from Users group by [name]
2. Select the id item in Table tmp1 from the Users table and write the data with the corresponding id to table tmp2.
Copy codeThe Code is as follows: select * into tmp2 from Users where [id] in (select [id] from tmp1)
3. Drop the Users and tmp1 tables.
Copy codeThe Code is as follows: drop table Users
Drop table tmp1
4. Rename tmp2 table to User table

[Note] If there is no primary key id, you can add an id field as follows:
Copy codeThe Code is as follows: select identity (int, 1, 1) as autoID, * into tmp0 from Users

[Scenario 3]
Assume that there is a User table with id as the primary key field, but there are some completely repeated items. Now we need to remove the completely repeated items in Users and keep only one item.

1. Store non-repeated data in the tmp1 table
Copy codeThe Code is as follows: select distinct * into tmp1 from Users
2. Delete the Users table
Copy codeThe Code is as follows: drop table Users
3. import data from Table tmp1 to the Users table.
Copy codeThe Code is as follows: select * into Users from tmp1
4. Delete the tmp1 table
Copy codeThe Code is as follows: drop table tmp1

How to delete duplicate records in SQL Server 2000

1. If a primary key exists: (id is the primary key)
Delete from tablename where id not in
(Select max (id) from aa group by year, month, day)

2. If it does not exist, it can only be like this:
-- Import is not repeated to the temporary table
Select distinct * into # temp from tablename
-- Clear the original table
Truncate table tablename
-- Reverse from temporary table to original table
Insert into tablename select * from # temp

Delete duplicate records for two tables in different databases in SQL Server

Use exists:

Delete from B where exists (select 1 from a where a. name = B. name and a. id = B. id and a. age = B. age and ....)