CODEWITHSUNDEEP
mysqlincrementinsert-update
WindBridges
WindBridges

Reputation: 753

MySQL: mass insert new records or increment existing

I need to make something like this:

INSERT `stats` (`id`,`clicks`) VALUES 
(1, clicks+7),
(2, clicks+3),
....
ON DUPLICATE KEY UPDATE `clicks`=VALUES(clicks)

in other words, when table has no record with pk id - it inserted and clicks gets 7 (or 3). When record with PK is present, then old value of click should be increased by 7 (or 3). As you can see, increment value for each row is different. Current query always overwrites old value. Please help to modify this query.

Upvotes: 0

Views: 91

Answers (1)

tadman
tadman

Reputation: 211610

The VALUES have to be literal values, not references to columns:

INSERT INTO `stats` (`id`,`clicks`) VALUES 
  (1, 7),
  (2, 3)
  ON DUPLICATE KEY UPDATE `clicks`=`clicks` + VALUES(`clicks`)

Upvotes: 2

Related Questions