MySQL GROUP BY export causing issues

Question

I am having an issue exporting data from a MySQL database. Essentially, I have some records in the database that are duplicates (some users signed up twice and there was no server side code to stop them from doing so).

So, to get all of the distinct values, I use this:

SELECT *
FROM users
GROUP BY email
ORDER BY user_id;

This is pretty straight forward and gives me exactly the data I want so I can rebuild the database. The issue is that when the data is exported from phpMyAdmin in a sql file, there are no quotes around the values in the email field. So, reimporting this into a database throws an error.

How can I get the data without having to put quotes around 13,000 email address manually so I can reimport this data??

You could write a script to put quotes around the email addresses — Jay Nebhwani, Oct 21 '13 at 00:19
Could you just do something like SELECT '"' + email + '"' as email. — jcwrequests, Oct 21 '13 at 00:20
@JayNebhwani Because there are 1400 of them. How would I do that in MySQL? — Sethen, Oct 21 '13 at 00:49
I mean with a query which involves the `DELETE` statement, not manually. There must be a way, Google it. — Jay Nebhwani, Oct 21 '13 at 00:51
http://stackoverflow.com/questions/2630440/how-to-delete-duplicates-on-mysql-table — Jay Nebhwani, Oct 21 '13 at 01:07

score 1 · Answer 1 · answered Oct 21 '13 at 00:31

Create a copy of the table using a script based on the below code

SELECT CONCAT('''', email, '''') AS email, rest_of_your_columns
FROM users
GROUP BY email, rest_of_your_columns
ORDER BY user_id
INTO new_users_table

In this case, the new table is called "new_users_table". It's the same table as your original table except all emails have single quotes encompassing them now.

MySQL GROUP BY export causing issues

1 Answers1