-1

Let's say I have dynamic numbers with unique id's to them.

I'd like to insert them into database. But if I already have that certain ID (UNIQUE) I need to add to the value that already exists.

I've already tried using "ON KEY UPDATE" ,but it's not really working out. And selecting the old data so we could add to it and then updating it ,is not efficient.

Is there any query that could do that?

Elvinas Samusis
  • 1
  • Possible duplicate of [PHP MYSQL UPDATE if Exist or INSERT if not?](http://stackoverflow.com/questions/6853190/php-mysql-update-if-exist-or-insert-if-not) – Mark Amery Dec 27 '15 at 17:16
  • *"I've already tried using "ON KEY UPDATE" ,but it's not really working out"* - in what way? `ON DUPLICATE KEY UPDATE` is the right tool for the job here - something like `"INSERT INTO Foo SET bar=:bar ON DUPLICATE KEY UPDATE bar=bar+:bar"` should do the trick - but "it's not really working out" isn't an error description that we can debug. – Mark Amery Dec 27 '15 at 17:19

2 Answers2

0

Incrementing your value in your application does not guarantee you'll always have accurate results in your database because of concurrency issues. For instance, if two web requests need to increment the number with the same ID, depending on when the computer switches the processes on the CPU, you could have the requests overwriting each other.

Instead do an update similar to:

UPDATE `table` SET `number` = `number` + 1 WHERE `ID` = YOUR_ID

Check the return value from the statement. An update should return the number of rows affected, so if the value is 1, you can move on happy to know that you were as efficient as possible. On the other hand, if your return value is 0, then you'll have to run a subsequent insert statement to add your new ID/Value.

This is also the safest way to ensure concurrency.

Hope this helps and good luck!

ohiodoug
  • 1,493
  • 1
  • 9
  • 12
  • This doesn't answer the question. The OP specified that there may not be an existing row to update. – Mark Amery Dec 27 '15 at 17:19
  • I respectfully disagree. The only way for the OP to know the ID is in the DB in the first place is either to select for it or to try to run an update and count the rows returned. In the select-first solution, he ALWAYS has to execute two queries, in the latter solution if the update returns 1, then he does not have to run a second query and if it returns 0 then he knows the row doesn't exist and he can add it. This is the most efficient. – ohiodoug Dec 27 '15 at 18:07
  • 1
    Sorry, I missed that detail in your answer, but still no: doing an update, checking for the number of updated rows, and then possibly doing an insert isn't a correct solution. It suffers from roughly the same race condition that you describe in your first paragraph unless you manually wrap the update and insert in a transaction. The right way to do this is with `ON DUPLICATE KEY UPDATE`; for example, a query like `INSERT INTO \`table\` SET \`number\` = 1, \`ID\` = YOUR_ID ON DUPLICATE KEY UPDATE \`number\` = \`number\` + 1;` That way you're guaranteed to only need a single query. – Mark Amery Dec 27 '15 at 18:34
  • Good points. I assumed an auto generated value, which wouldn't have been an issue but if it is a key that has additional meaning and is part of the insert, then yes, there is still an issue and the DUPLICATE KEY UPDATE is the way to go. – ohiodoug Dec 27 '15 at 18:35
-1

Did something different. Instead of updating the old values ,I'm inserting new data and leaving old one ,but using certain uniques so I wouldn't have duplicates. And now to display that data I use a simple select query with sum property and then grouping it by an id. Works great ,just don't know if it's the most efficient way of doing it.

Elvinas Samusis
  • 1
  • Everyone missed the point that I said ,I have dynamic numbers. There's no way in hell i could format those from loops to be accepted by MySQL. The only option is the one I wrote. And all of your answers are just basic stuff that already exists in every corner possible. – Elvinas Samusis Dec 28 '15 at 03:17