CODEWITHSUNDEEP
mysql
GoldenJoe
GoldenJoe

Reputation: 8002

How can I combine rows that have a matching column?

I have a table with the following structure:

+----+------+------+----------------+---------------------+
| id | val1 | val2 |     email      |     lastUpdated     |
+----+------+------+----------------+---------------------+
|  1 |    0 |    3 | [email protected] | 2015-02-07 13:21:55 |
|  2 |    5 |    0 | [email protected] | 2015-02-07 09:12:27 |
+----+------+------+----------------+---------------------+

Due to an error in a query I made, there are multiple rows with the same "email" value. I would like to merge val1, val2, and lastUpdated (greater value) for all duplicates. It would give a result like this:

+----+------+------+----------------+---------------------+
| id | val1 | val2 |     email      |     lastUpdated     |
+----+------+------+----------------+---------------------+
|  1 |    5 |    3 | [email protected] | 2015-02-07 13:21:55 |
+----+------+------+----------------+---------------------+

Upvotes: 1

Views: 42

Answers (2)

Amadan
Amadan

Reputation: 198324

Assuming that all the valX columns are all zero except for in one row,

RENAME TABLE originalTable TO messedUpTable
;
CREATE TABLE originalTable
SELECT
  id,
  SUM(val1) AS val1,
  SUM(val2) AS val2,
  email,
  MAX(lastUpdated) AS lastUpdated
FROM messedUpTable
GROUP BY email
;
-- check originalTable to be sure it is okay
;
DROP TABLE messedUpTable

If you had indices on originalTable, you'll have to define them again.

SQLFiddle with index examples put in.

Upvotes: 2

Petar Ivanov
Petar Ivanov

Reputation: 93020

select max(val1), max(val2), email, max(lastUpdated)
from your_table
group by email

Upvotes: 1

Related Questions