?
Solved

How di I need to delete any record in a MySQL table that is a duplicate?

Posted on 2012-09-14
3
Medium Priority
?
477 Views
Last Modified: 2012-09-15
I am working with a Perl generated csv file that is being imported into a MySQL database.

The command I am using is:

LOAD DATA LOCAL INFILE 'D:/all_cus.csv'
INTO TABLE stock_cus FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'
( LegalName,sym,cus,email,entire_address,phone_number
);

I can then locate all of the duplicate values of 'cus' with:
SELECT LegalName,sym,a.cus FROM stock_cus a
INNER JOIN (SELECT cus FROM  stock_cus
GROUP BY cus DESC HAVING count(cus) > 1) dup ON a.cus = dup.cus;

This gives:
+--------------------------------+------+-----------+
| LegalName                      | sym  | cus       |
+--------------------------------+------+-----------+
| Communications Systems Inc.    | JCS  | 203900105 |
| Communications Systems Inc.    | JCS  | 203900105 |
| Sangamo BioSciences Inc.       | SGMO | 800677106 |
| Unitek Global Services Inc.    | UNTK | 911363109 |
| United Rentals Inc.            | URI  | 911363109 |
| Vascular Solutions Inc.        | VASC | 800677106 |
| Vanguard REIT ETF              | VNQ  | 922908553 |
| Vanguard Total World Stock ETF | VT   | 922908553 |
| Encore Wire Corp.              | WIRE | 292562105 |
| Encore Wire Corp.              | WIRE | 292562105 |
| Wipro Ltd ADR                  | WIT  | 97651M109 |
| Wipro Ltd ADR                  | WIT  | 97651M109 |
| Westmoreland Coal Co.          | WLB  | 960878106 |
| Westmoreland Coal Co.          | WLB  | 960878106 |
| Westlake Chemical Corp.        | WLK  | 960413102 |
| Westlake Chemical Corp.        | WLK  | 960413102 |
+--------------------------------+------+-----------+

What I need to do is delete ALL rows in the above list. I do not want to only delete one duplicate, but both.

What is the mysql command that will do this?

I am unable to figure it out.

Thanks
0
Comment
Question by:John_2357
3 Comments
 
LVL 3

Assisted Solution

by:Hermani
Hermani earned 1000 total points
ID: 38401183
Not really familiar with mySQL syntax but normally you can just replace select with delete ( without specifiing fields )

so

Delete FROM stock_cus a
INNER JOIN (SELECT cus FROM  stock_cus
GROUP BY cus DESC HAVING count(cus) > 1) dup ON a.cus = dup.cus;

Open in new window


Should do the trick
0
 
LVL 25

Accepted Solution

by:
lwadwell earned 1000 total points
ID: 38401205
@Hermani has it right except for the MySQL syntax, I think the correct syntax will be:
DELETE a FROM stock_cus a
INNER JOIN (SELECT cus FROM  stock_cus
GROUP BY cus DESC HAVING count(cus) > 1) dup ON a.cus = dup.cus;

Open in new window

0
 
LVL 1

Author Closing Comment

by:John_2357
ID: 38401925
Many Thanks to both of you!
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month15 days, 13 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question