Reputation: 11
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
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