Posted on May 20, 2017 at 10:23 am
Fastest ways to find duplicate records in MySQL.
If name is the column you want to find duplicates in:
SELECT col1 FROM table GROUP BY col1 HAVING count(*) > 1; |
Is also possible to support multiple columns, like this:
SELECT col1, col2, COUNT(*) c FROM table GROUP BY col1, col2 HAVING c > 1; |
Get the IDs of the duplicate rows with GROUP_CONCAT:
SELECT GROUP_CONCAT(id), name, COUNT(*) c FROM table GROUP BY name HAVING c > 1; |
Other Posts
- Detect VMWare Virtual Machine
- Detect Microsoft Virtual PC Virtual Machine
- Fix MariaDB 10.5 "Can't create test file" errors
- Use MariaDB and MySQL without Password (MySQL Backup)
- Check if a Trademark is already registered
- Make name server address permanent in /etc/resolv.conf
- InnoSetup error the servicemanager is not available
- InnoSetup disable DesktopIcon via command-line
Updated Posts
- Delphi 10 Berlin System.Hash MD5 SHA1 SHA2 Hash
- Use cURL to check for SSL certificate issues:
- How to test Socks5 proxy with cURL
- How to use variables in a sed command
- Best Socks5 Servers for Linux Debian
- Error NO_PUBKEY in Google Cloud Debian Packages Update
- How to Run a Command with Time Limit in Bash Linux
- MySQL InnoDB log sequence number is in the future