-1

I'm moving some old code from SQLite to my website to be accessed through a website. One of the queries that I used quite often was the INSERT or REPLACE INTO query, which checked to see if a row existed in the table (Based on the first value) and if it did, it would replace the data in that row with the row specified in the VALUES. However if the row was not found it would INSERT the row into the table.

Query Example (Reduced):

INSERT or REPLACE INTO player_currency (player_id, gold) VALUES (1, 500)

This would find the player with an ID of 1 and set his gold to 500, regardless as to if he was in the table before or not.

I'm not sure if it matters, but I'm using XAMMP 5.6.8 (I've added PHP to the tags considering that's what's being used to connect to the database, feel free to remove it if it doesn't belong)

  • Dabatase Connection: (PHP) PDO Library
  • MySQL Version: 5.6.24
  • PhpMyAdmin Version: 4.4.3
Ahmad
  • 12,336
  • 6
  • 48
  • 88
Hobbyist
  • 15,888
  • 9
  • 46
  • 98
  • 1
    Check this one out: http://stackoverflow.com/questions/6107752/how-to-perform-an-upsert-so-that-i-can-use-both-new-and-old-values-in-update-par – dwjv Jul 03 '15 at 09:55
  • try [Replace](https://dev.mysql.com/doc/refman/5.0/en/replace.html) `replace player_currecy (player_id, gold) VALUES(1, 500)` – Double H Jul 03 '15 at 10:04

2 Answers2

2
INSERT INTO player_currecy (player_id, gold) VALUES(1, 500) ON DUPLICATE KEY UPDATE gold = VALUES(gold)

Just remember to have id as a primary key then.

Jesper
  • 3,816
  • 2
  • 16
  • 24
0

Assuming that player_id is a primary key, you can do this:

INSERT INTO player_currency (player_id, gold) VALUES (1,500) ON DUPLICATE KEY UPDATE gold=500;
Saty
  • 22,443
  • 7
  • 33
  • 51
Sam King
  • 90
  • 1
  • 5
  • "it would replace the data in that row with the row specified in the VALUES", since he wants the value he enters into VALUES, then i guess it can vary, and then this sql wouldn't work proberly, since this would always insert 500, and not dynamically insert the same value. – Jesper Jul 03 '15 at 10:02
  • It would work, he just needs to set his code up to set the value twice in the SQL string. I use it all the time. – Sam King Jul 03 '15 at 10:08
  • Negated your -1 simply because you answered the question, however Djip's answer is correct as it adapts to the value without the need of hard-coding. – Hobbyist Jul 03 '15 at 10:56