7

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?

surfmuggle
  • 4,724
  • 6
  • 38
  • 68
Not Amused
  • 872
  • 2
  • 8
  • 26
  • My guess is that this would be simpler with 2(ish) queries and an if/else block. Is there a particular reason you're wanting to do it this way? – landons Nov 20 '12 at 04:36
  • 2
    Getting the most out of a language's potentions is good practice. It's more about discovering things, and make things the faster way possible. – Not Amused Nov 20 '12 at 04:39
  • I really hope this has [proper SQL escaping](http://bobby-tables.com/) if it's deployed to production. Seeing naked variables like that is worrying. – tadman Nov 20 '12 at 04:39
  • @DeusDeceit: Ah, I get that entirely. I think you're out of luck with this, but you could try a stored procedure instead ;) – landons Nov 20 '12 at 04:42
  • All variables are escaped Mr. tadman and used the right way. If you have something more to suggest then please do. – Not Amused Nov 20 '12 at 04:43
  • Here is the answer of your problem [link](http://stackoverflow.com/questions/12436602/if-exists-update-else-insert-haunted-me-for-hours-new-to-mysql/12437127#12437127) – Sohail Ahmed Nov 20 '12 at 04:45
  • Hi @NotAmused i assume that `boardid == SHOP_ID`. Is this the case? If you want to transfer points from one shop to another do you keep the same player id? Does Player with ID 7 has this ID in all shops? Or could it be that the Player `Jane Doe` has the `player_id=7` in `shop_id=2` and this same person has `player_id=29` in `shop_id=3`? – surfmuggle May 18 '21 at 15:11

2 Answers2

3

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.

surfmuggle
  • 4,724
  • 6
  • 38
  • 68
tadman
  • 194,930
  • 21
  • 217
  • 240
  • I like this solution, but I never used triggers before I guess it's time for me to do so. As about escaping... $from = (int)mysql_real_escape_string($_REQUEST['from']); $to = (int)mysql_real_escape_string($_REQUEST['to']); $delete = $condition[mysql_real_escape_string($_REQUEST['delete'])]; $contest = $condition[mysql_real_escape_string($_REQUEST['contest'])]; is there anything more I could do? – Not Amused Nov 20 '12 at 04:54
  • Yes. Triggers would be the logical next step. Automated deletes on constraint violations was an ambitious thought :) – landons Nov 20 '12 at 04:55
  • Yes... use PDO (or another DB abstraction library). – landons Nov 20 '12 at 04:55
  • Any use of `mysql_real_escape_string` in the year 2012 is terrifying. Please, for your own sanity use something sensible like PDO. With SQL placeholders this would *literally* collapse to two easy, readable lines. If you use `?` or, better, named parameters, then you're just a `bindParam` call or two away from safety. It takes all of [thirty minutes](http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/) to learn how to use PDO effectively. – tadman Nov 20 '12 at 04:57
  • This is also the "Help, I'm having trouble with my stone arrow, how to spear mastadon" type of answer. There are many good [ORM database layer](http://stackoverflow.com/questions/108699/good-php-orm-library) packages for PHP that would make this kind of database work considerably easier. Making low-level SQL calls and hand-writing queries is not fun and not very productive. – tadman Nov 20 '12 at 04:59
  • On another thread, people were suggesting to use mysqli, you're saying pdo... A brief description would be nice. I am new to all this, and I do want security, even tho this project is not supposed to be visited much. – Not Amused Nov 20 '12 at 05:03
  • PDO is arguably the best. `mysqli` is what you use if, for whatever reason, you can't use PDO. I've linked to the [tutorial](http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/) there. It's not just about security. It's about doing it right so you don't have nasty bugs later when "Jimmy O'Neil" signs up on your app. Still, unless you aspire to have a long career of shovelling piles of messy SQL statements, you should try an ORM. – tadman Nov 20 '12 at 05:09
  • Isn't that what mysql_real_escape_string does? Prevents having problems when "Jimmy O'Neil" sings on my app? I don't really know what's the difference, but I will look into it, that's for sure. – Not Amused Nov 20 '12 at 05:12
  • Yes, `mysql_real_escape_string` is intended to fix that, but `mysql_query` is dangerous by default. If you miss one of these, even accidentally, it could lead to severe problems. There are [SQL injection tools](http://sqlmap.org/) that can automatically find and exploit even the tiniest oversight. Using PDO and being disciplined about using SQL placeholders is the easiest way to be sure you're doing it right. [`mysql_query`](http://php.net/manual/en/function.mysql-query.php) is deprecated, the community is trying to kill it off. See the big red box on the documentation! Please, do not use it. – tadman Nov 20 '12 at 05:17
0

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.

landons
  • 9,374
  • 3
  • 31
  • 46