mysql insert and update on duplicate

Question

I have table with several cols and I'm doing a multiplier insert to it.

ie:

INSERT INTO accounting_positions
KEYS (`date`, `customer`, `type`, `product`, `volume`, `payout`, `count`)
VALUES ('2012-01-10','10','regular','60sec',65,83,13),
('2012-01-10','10','regular','binary',15,83,13)
ON DUPLICATE KEY UPDATE volume=volume+CURRENT_COL_VAL;

what should I write instead of the "CURRENT_COL_VAL"? if I want to update the duplicate row with a value from my insert.

because I cant place a static number inside it because it is differs with every row insert in the multiple insert

where do you get from the static numbers above? Is there any of them being the volume? — Sebas, May 12 '13 at 12:40
I edited the insert query so you could see the table keys, with every insert row you get different data so each insert the update on duplicate key is differing — Tzook Bar Noy, May 12 '13 at 12:43
so why are you putting a hardcoded "65" value for `volume` here since it changes all the time? — Sebas, May 12 '13 at 12:48

score 2 · Accepted Answer · answered May 12 '13 at 12:51

have a look at the mysql docu on insert-on-duplicate From there you can see that you can use something like this:

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
  ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

in your case:

INSERT INTO accounting_positions
    (`date`, `customer`, `type`, `product`, `volume`, `payout`, `count`)
VALUES 
    ('2012-01-10','10','regular','60sec',65,83,13),
    ('2012-01-10','10','regular','binary',15,83,13)
ON DUPLICATE KEY UPDATE volume=volume+VALUES(volume);

which would add the new and the old value. If you only want the given insert value to overwirite the row then do: ON DUPLICATE KEY UPDATE volume=VALUES(volume)

mysql insert and update on duplicate

1 Answers1