delete duplicate records in sql

The table structure is as follows: Mysql> desc tbl; + ------- + -------------- + ------ + ----- + --------- + ---------------- + | FIELD | type | null | key | default | extra | + ------- + -------------- + ------ + ----- + --------- + ---------------- + | id | bigint (20) | no | pri | null | auto_increment | word | varchar (255) | no |-| state | char (1) | no | I | + ------- + -------------- + ------ + ----- + --------- + ---------------- + The statement for deleting

You can delete duplicate records in the following ways:For example, table name: dbo. Description $. The fields include ID_PK, breed name, traits standard number, and code. First, create a table with the same structure as the original table:Copy codeThe Code is as follows:Select * into tmpA from dbo. breed description $ where 1 = 2 -- createdIn the data table, the

; 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

Label: Delete from where not inch (Selectmin from,select*from asGroup by t.id); Most of the online searches are as follows Delete frompeoplewherePeopleidinch(SelectPeopleid fromPeopleGroup byPeopleid having Count(Peopleid)> 1) androwID not inch(Select min(ROWID) fromPeopleGroup byPeopleid having Count(Peopleid)>1) As a result, you will find that MySQL has an error because it cannot be updated wit

1. Querying duplicate Record single fieldsSELECT * from TBL a where exists (select 1 to tbl B where A.username=b.username GROUP by username have count (*) > 1)2. Number of query repetitionsSelect Clistname,count (clistname) from Clalist GROUP by Clistname have Count (*) >13. Delete duplicate records, leaving ROWID mini

You can delete duplicate records in the following ways: For example, table name: dbo. Description $. The fields include ID_PK, breed name, traits standard number, and code. First, create a table with the same structure as the original table: Copy codeThe Code is as follows: select * into tmpA from dbo. breed description $ where 1 = 2 -- created In the data tab

Tags: style blog class Code color HTTPFirst, the outside chapterToday is very sad urge ah, to user data to do Datapatch, the data of each month more imported a copy, instantly startled out in a cold sweat ... This is the product environment, if the boss knows it can be dead, hurriedly remove duplicate records, and write down the following article for later use. Ii. Preparation of the article1. First create

Table Structure:Create table tab_name(Id tinyint,Name varchar (10 ),Age tinyint,Phone varchar (15 ));Original content: select * from tab_name;Id name age phone1 zhangsan 10 123456789102 lisi 28 987654321103 wangwu 33 456789123124 zhaoliu 45 789456123542 lisi 28 987654321103 wangwu 33 45678912312Search after deduplication: select * from tab_name group by id, name, age, phone;Id name age phone1 zhangsan 10 123456789102 lisi 28 987654321103 wangwu 33 456789123124 zhaoliu 45 78945612354Create anothe

Go to: http://blog.163.com/aner_rui/blog/static/12131232820105901451809/2. Keep One (this should be what most people need ^_^)Delete hzt Where ID not in (the Select Max (ID) from Hzt Group by Title)Note: The maximum one record for the ID is retained here1, look for redundant records in the table, duplicate records are