SQL de-duplication

Source: Internet
Author: User

SELECT *
From Tb_music_mp3
WHERE (Songtype in
(SELECT Songtype
From Tb_music_mp3
GROUP by Songtype
Having (COUNT (songtype) > 1)))

SELECT *
From tb_usertable_other_100000
WHERE (userid in
(SELECT userid
From tb_usertable_other_100000
GROUP by UserID
Having (COUNT (userid) > 1)))

SELECT *
From Tb_usertable_main
WHERE (userid in
(SELECT userid
From Tb_usertable_main
GROUP by UserID
Having (COUNT (userid) > 1)))
ORDER BY UserID


Delete
From tb_usertable_other_5600000
WHERE (id not in
(SELECT MIN (ID)
From tb_usertable_other_5600000
GROUP by UserID))

In large database applications, repeated records are often encountered for various reasons, resulting in redundancy of data and inconvenience of maintenance.
ID Custom
1 Chemical Company
2 Chemical Company
3 Chemical Company
4 Software Companies
5 Pharmaceutical Companies
6 Pharmaceutical Companies

Now would like to leave a duplicate, repeat the deletion, the result is as follows:
ID Custom
1 Chemical Company
4 Software Companies
5 Pharmaceutical Companies

Delete table where ID not in (select min (id) from table Group by custom)

SELECT *
From Tb_music_mp3
WHERE (id not in
(SELECT MIN (ID)
From Tb_music_mp3
GROUP by Songtype))

SELECT ID, Songtype, songname
From Tb_music_mp3 A
WHERE (Not EXISTS
(SELECT 1
From Tb_music_mp3 b
WHERE A.songtype = B.songtype and a.id > b.ID))

1. Using the rowID method

2. Using the group by method

3. Using the Distinct method

1. Using the rowID method

According to the ROWID attribute of the Oracle band, it is judged whether there are duplicates, as follows:
Check the data:
SELECT * FROM table1 a where rowid! = (select Max (ROWID)
From table1 b where a.name1=b.name1 and a.name2=b.name2 ...)
Delete data:
Delete from table1 a where rowid! = (select Max (ROWID)
From table1 b where a.name1=b.name1 and a.name2=b.name2 ...)

2.group by method

Check the data:
Select COUNT (num), max (name) from student--Lists the number of duplicate records and lists his Name property
GROUP BY Num
Having count (num) >1--grouped by Num to find duplicates of num columns in a table that occur more than once
Delete data:
Delete C
In this case, all the duplicates are deleted.

3. Using the distinct method-useful for small tables

CREATE TABLE Table_new as SELECT DISTINCT * FROM table1 Minux
TRUNCATE TABLE table1;
INSERT INTO table1 select * from Table_new;

Delete
From Tb_book_list
WHERE (id not in
(SELECT max (ID)
From Tb_book_list
GROUP by BookID))
(5065 rows affected)

SQL de-duplication

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.