Delete a duplicate data SQL statement

Source: Internet
Author: User
Tags rowcount

Delete a duplicate data SQL statement
Method One

Suppose there is a duplicate field of name,address that requires a unique result set for both fields
Select Identity (int,1,1) as Autoid, * into #tmp from TableName
Select min (autoid) as autoid into #tmp2 from #tmp Group by name,autoid
SELECT * from #tmp where autoid in (select Autoid from #tmp2)

Method Two
Declare @max integer, @id integer
Declare cur_rows cursor Local for select main field, COUNT (*) from table name Group by main field having count (*) > 1
Open Cur_rows
Fetch cur_rows into @id, @max
While @ @fetch_status =0
Select @max = @max-1
Delete from table name where main field = @id
Fetch cur_rows into @id, @max
Close Cur_rows

Method Three

If the table needs to delete duplicate records (1 of duplicate records are retained), you can delete them in the following ways
SELECT DISTINCT * into #tmp tablename
SELECT * INTO TableName from #tmp
drop table #tmp


Vieworder ID number Punching machine consumption type time price
1 A1 1th, breakfast 2006-08-03 07:09:23.000 2
2 A1 1th, breakfast 2006-08-03 07:10:13.000 2
3 A1 1th, breakfast 2006-08-03 07:10:19.000 2
4 A1 1th, lunch 2006-08-03 12:02:10.000 5
5 A2 1th, lunch 2006-08-03 12:11:10.000 5
6 A2 1th, lunch 2006-08-03 12:12:10.000 5


Delete from Table A
where exists (select * from table where consumption type =a. Consumption type and time >=dateadd (minute,-2,a. Time) and time <a. Time)

View the data to be deleted with the SELECT statement before deleting
from Table A
where exists (SELECT * from table where  consumption type =a. Consumption type and Time >= DATEADD (minute,-2,a. Time) and time <a. Time)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.