MySQL merging rows with equivalent ID

Question

I have an issue where a mistake resulted in a database table having both emails and GUIDs mixed in the ID column.

The table looks like this (simplified):

GUID | Value | Date
cf@a |   21  | 2016
mf@b |   42  | 2015
mf@b |   21  | 2016
1aXd |   3   | 2016
a7vf |   9   | 2015

Where for example user cf@a and user 1aXd are the same. The GUID - Email combinations are stored in another table. The emails are unique. Primary key is the ID(GUID) and the Date combined. My problem is, how do i update the table and merge the rows? the Value column of two merging rows should be summed.

So the example table assuming (cf@a -> 1aXd and mf@b -> 8bga and ui@q -> a7vf) would become:

GUID | Value | Date
1aXd |   24  | 2016   <-- merged, Value = 21+3
8bga |   42  | 2015   <-- converted to GUID
8bga |   21  | 2016   <-- converted to GUID
                      <-- one row removed (merged with the first  one)
a7vf |   9   | 2015   <-- untouched

Thank you for any help!

I could do this in C# but i would rather learn how to do it with the MySQL Workbench

Nick · Accepted Answer · 2016-08-26 07:15:05Z

1

Use JOIN:

SELECT t1.Value + t2.Value
FROM t1 
JOIN t2 USING (`GUID`)

If you want update values, you need something like this:

UPDATE t1
JOIN t2 USING (`GUID`)
SET t1.Value = t1.Value + t2.Value

Removing merged rows:

DELETE t2 FROM t2
JOIN t1 USING (`GUID`)

UPDATE

If has only one table.

Merge:

UPDATE t1
JOIN (
    SELECT GUID, SUM(Value) as amount, COUNT(1) as cnt 
    FROM t1
    GROUP BY `GUID`
    HAVING cnt > 1
) t2 ON t2.GUID = t1.GUID
SET t1.Value = t2.amount;

Delete:

CREATE table t2 (
    GUID integer,
    Value integer,
    Date integer
);

INSERT INTO t2 (GUID, Value, Date)
SELECT GUID, Value, MAX(Date) FROM t1 GUID, Value;

Result will be in t2.

edited Aug 26, 2016 at 7:15

answered Aug 26, 2016 at 6:11

Nick

10.2k8 gold badges65 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

maxx Over a year ago

but this doesnt remove rows?

Nick Over a year ago

@maxx I updated answer with example of removing merged rows

maxx Over a year ago

I'm not sure i understand, wont that remove all the merged rows? I have just one table t1 inside which i want to merge rows.

Collectives™ on Stack Overflow

MySQL merging rows with equivalent ID

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related