update on duplicate key update

2018-06-03 08:17:56

I have a table that contains a shop id and a player's id, and a player's points. What I want to do is transfer points from a shop to the other, the thing is that 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`=.... get the idea?

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.

No. You can't delete a record upon constraint violation in MySQL. You could possibly do a before update trigger that checks for an impending constraint violation, but even then (as of 5.1) you can't modify that same table's data (which would likely cause an endless loop in this case anyway).

Only halfway finished before Tadman's answer. I like his idea, personally.

链接地址: http://www.djcxy.com/p/11448.html

上一篇: 在没有setZOrderOnTop（true）的情况下使SurfaceView透明

下一篇: 更新重复密钥更新