SQL Delete duplicate records

Source: Internet
Author: User

with SQL statements, delete duplicates only keep oneThe addition of the HAVING clause in SQL is because the WHERE keyword cannot be used with the aggregate function.

All of the aggregate functions are shown in the following table:

MIN
Returns the smallest value in a given column
MAX
Returns the largest value in a given column
SUM
Returns the sum of all the values in a given column
Avg
Returns the average of all the values in a given column
COUNT
Returns the number of all values in a given column
COUNT (*)
Returns the number of rows in a table assuming that we will search the employee table for the highest-paid column, you can use the following SQL statement:

SELECT Max (Salary), Dept

From employee

GROUP by dept;

This statement will select the highest wage wage in each individual department. As a result their salary and dept will be returned. SELECT Dept, AVG (Salary) per department, average salary

From the employee from this table

GROUP by dept Only one display per department, the same department is not displayed.

Having avg (salary) > 20000; Display that specifies the average value greater than how much

in thousands of records, there are some same records, how can you use SQL statements, delete duplicates
1, look for redundant records in the table, duplicate records are based on a single field (Peopleid) to determine
SELECT * from people
where Peopleid in (select Peopleid from People GROUP by Peopleid have count (Peopleid) > 1)

2, delete redundant records in the table, duplicate records are based on a single field (Peopleid) to judge, leaving only the ROWID (address) the smallest record
Delete from people
where Peoplename in (select Peoplename from People GROUP by Peoplename have count (peoplename) > 1)
and Peopleid not in (select min (peopleid) from people GROUP by Peoplename have Count (peoplename) >1)

3. Find redundant duplicate records (multiple fields) in the table
SELECT * from Vitae a
where (A.PEOPLEID,A.SEQ) in (select Peopleid,seq from Vitae GROUP by PEOPLEID,SEQ have count (*) > 1)

4. Delete extra duplicate records (multiple fields) in the table, leaving only the record with ROWID minimum
Delete from Vitae a
where (A.PEOPLEID,A.SEQ) in (select Peopleid,seq from Vitae GROUP by PEOPLEID,SEQ have count (*) > 1)
and rowID not in (select min (rowid) from Vitae GROUP by PEOPLEID,SEQ have Count (*) >1)

5. Find redundant duplicate records (multiple fields) in the table, not including the smallest ROWID records
SELECT * from Vitae a
where (A.PEOPLEID,A.SEQ) in (select Peopleid,seq from Vitae GROUP by PEOPLEID,SEQ have count (*) > 1)
and rowID not in (select min (rowid) from Vitae GROUP by PEOPLEID,SEQ have Count (*) >1)

6. Remove the first bit to the left of a field:

Update TableName Set [Title]=right ([title], (Len ([title])-1)) where Title like ' Village% '

7. Remove the first bit to the right of a field:

Update TableName Set [Title]=left ([title], (Len ([title])-1)) where Title like '% Village '

8. False Delete extra duplicate records (multiple fields) in the table, not including ROWID minimum records
Update vitae set Ispass=-1
where Peopleid in (select Peopleid from Vitae GROUP by Peopleid

SQL Delete duplicate records

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: info-contact@alibabacloud.com 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.