0

I have the following table structure

'category_id', 'int(11)', 'PRI'
'page_id', 'int(11)', 'PRI'
'current_week_visits', 'int(11)'

I keep updating the current week visits daily on this table and I would like to insert a row if (app_id, page_id) is not present in the table. Else, update the current_week visits

I tried,

INSERT INTO visits (category_id, page_id, current_week_visits) VALUES (150, 1, 100) ON DUPLICATE KEY UPDATE current_week_visits = current_week_visits + 100;

But it does not seem to save the current week visits. Could you throw some light on what's wrong here?

user2246674
  • 7,621
  • 25
  • 28
vasa
  • 787
  • 8
  • 21
  • UPSERT? Is that what they call it when INSERT and UPDATE are dating? – Barmar Jul 19 '13 at 22:03
  • @Barmar this is a valid term of art, although the more correct term would be `MERGE` – Explosion Pills Jul 19 '13 at 22:05
  • Your query works for me: http://www.sqlfiddle.com/#!2/4712e/1 – Barmar Jul 19 '13 at 22:08
  • @vasa the query seems valid to me; do you have more details? – Explosion Pills Jul 19 '13 at 22:08
  • @vasa it also works perfectly for me. have you tried making an http://www.sqlfiddle.com ? see it working here: http://sqlfiddle.com/#!2/68718/1/0 – amaster Jul 19 '13 at 22:11
  • @vasa what version of mysql are you running? – ermagana Jul 20 '13 at 03:33
  • SQLfiddle is awesome. I didn't know about it until now. My table had null values for current_week_visits. I truncated the table and tried it again and it worked. Thanks guys. – vasa Jul 22 '13 at 03:48
  • https://github.com/seamusabshere/upsert is a Ruby library (disclosure: mine) that transparently creates an upsert function and allows composite primary keys - in fact, the "selector" doesn't have to be indexed at all – Seamus Abshere Nov 12 '13 at 22:48

0 Answers0