1

I ve already seen some questions regarding this like below MySQL “good” way to insert a row if not found, or update it if it is found

Now i have a summary table which gets updated with the qty every time say a sale occurs. so out of 1000 sales of an item only first time the insert executes and the rest of the times it would be update. My understanding is in Insert on Duplicate Key Update it tries to insert first and if it fails updates. so all 999 times the insert is not successfull

1) Is there a method to check Update first and if not updated then insert in a single statement?

2) which of the below methods would be desirable considering most of the cases update will be successfull

a) using Insert on Duplicate Key Update b) Call Update; if no rows affected call insert

Right now i am using the second option(b). performance gain is very important here and also i m testing the first option. ill post the results here once done

xGen
  • 474
  • 1
  • 8
  • 23
  • 2
    is there any code to show... what have u tried? – Vivek Singh Apr 14 '15 at 06:38
  • I think you can do this with `IF EXISTS` and `IF NOT EXISTS`. However to post a valid question you need to come with your tried first. I would suggest to use another language for it to check tho' – Mike M. Apr 14 '15 at 06:39
  • possible duplicate of [How to 'insert if not exists' in MySQL?](http://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql) if you look to the answer under section `REPLACE` this question is answered – Mike M. Apr 14 '15 at 06:47
  • @MikeM. Not a duplicate. It's not about how to do it, but more about why this way and not OP's different approach. – fancyPants Apr 14 '15 at 06:58

1 Answers1

0

INSERT ON DUPLICATE KEY UPDATE is the way to go. It does not perform a full insert or how you put it.
For this statement to work there has to be a primary key or unique key on the table and the corresponding columns have to be involved in the statement.
Before it's decided whether an insert or an update statement has to be done, the said keys are checked. This is usually really fast.

With your "update first" approach you gain nothing, no it gets even worse. The primary key lookup has to be done anyway. In the worst case you wasted time by having to look up the primary key two times. First for the update statement (which may not be necessary), then for the insert statement.

fancyPants
  • 46,782
  • 31
  • 80
  • 91
  • in the "update first" method i just check rowsaffected. if 0 then insert. no key lookup. Another thought was "most cases update will be successfull". so in those scenarios the question would be which is better "update" or "INSERT ON DUPLICATE KEY UPDATE". – xGen Apr 14 '15 at 07:14
  • How do you think MySQL gets to the "rows affected: 0" result? Magic? No, a key lookup. Like I said, `INSERT ON DUPLICATE KEY UPDATE` is the way to go... – fancyPants Apr 14 '15 at 07:26