update/insert one table upon data in another

BubikolRamios · 02-24-2012, 02:06 PM

reason: having a huge table which slows down db, need to do analyse daily and add that to existing analytics (avoid analyse on huge bunch of data each day)

table a (existing analyticsa)

Code:

a  5
b  10
d  30

table b (today analytics)

Code:

a  5
b  10
c  15

as a result of 'join' of those 2 tables a should become:

table a

Code:

How to go about that most efficently ?

Keleth · 02-24-2012, 03:29 PM

I'd be curious to hear if there is an efficient MySQL solution, but this sounds like a processing thing, and with that the solution would be to pull, sum, and insert.

I don't know if there is a method to sum where key's match.

BubikolRamios · 02-24-2012, 05:42 PM

On second thought:

Pseudocode:

Code:

insert into a
select from b
ON DUPLICATE KEY UPDATE
sum_field =  (a.sum_ + b.sum),