CODEWITHSUNDEEP
mysqlsqldatabase
Amir Namdar
Amir Namdar

Reputation: 11

MySQL - merge column value for rows with the same key with the same key

Heres a description screen shot of the keys in a table that I use: Table keys

Each row in the table is a representation of purchases by a specific client in a specific hour. So a typical row would be like: typical row (screenshot)

I need to merge two clients data, so one client will have all the purchases values summed up in his rows, for each hour.

In pseudo code what i want to perform is:

For every hour (row), Add the 'purchase amount' of the rows that have client id of '526' to all rows that have client id '518'

.

At first, I tried to execute this but then got an error due to the multiple keys configured in the table:

UPDATE purchases set client id = 518 where client id = 526;

since the client '518' already has rows for the same hours, I can not perform the above query that creates new rows.

How should I tackle this?

Upvotes: 0

Views: 615

Answers (1)

Koen
Koen

Reputation: 734

You will require three queries: One to do the sum if a record exists for both customers with the same time value:

update purchase p1 
    inner join purchase p2 on p2.client_id=528 and p2.date=p1.date
set p1.amount = p1.amount + p2.amount
where p1.client_id=526;

A second one to handle the records where only one exists (and not the one that will continue to exist):

insert into purchase 
    (select 526, date, amount 
     from purchase p1 
     where p1.client_id=528 and 
        not exists (select * 
                    from purchase p2 
                    where p2.client_id=526 and 
                          p2.date=p1.date));

Note - the above can probably also be done (and more elegantly) using an update query.

And a final query to remove the merged records:

delete from purchase where client_id=528;

Note - I used client_id values 526 and 528 throughout - you may need to alter these numbers to fit your purpose.

Upvotes: 1

Related Questions