MySQL batch Insert application ON DUPLICATE KEY UPDATE

MySQL batch Insert application ON DUPLICATE KEY UPDATE

It is not clear whether the problem is design or implementation.
At last, it was a strange demand for me.

Award_credit_room table, which stores user gift history
Award_credit is the point record generated by user gift giving.

After the data of award_credit_room is summarized, You need to Insert the data to the award_credit table in batches,
If no record exists, Insert the statement. If a record exists, Update the statement.

Use MySQL custom variables to implement insert... select... ON DUPLICATE KEY UPDATE

This function uses the following SQL

Set @ a: = 0;
Set @ B: = 0;
Insert into award_credit (credits, vvid, CreditsTotal)
Select @ a: = sum (CreditChange), VVID, sum (CreditChange)
From award_credit_room r
Where awardActId = 23 and Status = 2 group by VVID
On duplicate key update credits = credits + @ a, creditsTotal = creditsTotal + @;

Summary query results before execution
Select @ a: = sum (CreditChange), VVID, sum (CreditChange)
From award_credit_room r
Where awardActId = 23 and Status = 2 group by VVID

Award_credit table data before SQL Execution

Award_credit table data after SQL Execution

 

During the experiment, there was an optimization idea. Since both fields are sum aggregation, can we use custom variables to calculate them once,
In fact, the execution sequence and position of custom variables are not strictly fixed.

However, there are also workarounds.
Set @ a: = 0;
Insert into award_credit (credits, vvid, CreditsTotal)
Select @ a: = s, vvid, s from
(
Select sum (CreditChange) s, VVID from award_credit_room r where awardActId = 23 and Status = 2 group by VVID
) T1
On duplicate key update credits = credits + @ a, creditsTotal = creditsTotal + @;

This article permanently updates the link address: