update on duplicate key update
mysql, on-duplicate-key, sql-update
Solution
You can only make alterations in the context of one conflicting row in the `ON DUPLICATE KEY` area. Further, this is, as far as I know, a property of the `INSERT` statement.
What you need is a simple ledger where you record the additions and subtractions from a balance, then tabulate those either manually or using triggers.
For instance, the simplest approach is:
INSERT INTO points_adjustments (boardId_from, boardId_to, points)
VALUES (?, ?, ?)
This might be more easily represented as a pair of entries:
INSERT INTO points_adjustments (boardId, points)
VALUES (?, ?)
You'd add one entry for +n points, and a matching one for -n. At any time you can get a balance using `SUM(points)`. You could wrap this up in a `VIEW` to make retrieval easier, or if you want, denormalize the sums into a column of another table using a trigger.
A simple trigger would issue the following statement for each affected `boardId`:
INSERT INTO balances (boardId, points) VALUES (?, ?)
ON DUPLICATE KEY SET points=points+VALUES(points)
This avoids key collisions in the first place and provides an auditable record of the transactions that occurred.
In any case, to do all of this automatically you'd probably have to use a trigger.
3rd party edit
From the docs INSERT ... ON DUPLICATE KEY UPDATE Statement
In general, you should try to avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes.
Problem
I have a table `playerspoints` that contains a shop id and a player's id, and a player's points. ``` SHOP_ID | PLAYER_ID | POINTS ---------------------------------------- 1 | 7 | 66 2 | 4 | 33 ``` What I want to do is transfer points from a shop to the other. - Problem: shop id and players id form a unique index. - What I want to do is on duplicate key update, instead of let it fail, to add the points of one entry to the other and delete the "from" entry. Something like: ``` UPDATE `playerspoints` SET `boardId`=$to WHERE `boardId`=$from ON DUPLICATE KEY UPDATE `points`=.... ``` Do you get the idea?