CODEWITHSUNDEEP
mysqlsqlinserton-duplicate-key
Tzook Bar Noy
Tzook Bar Noy

Reputation: 11677

mysql insert and update on duplicate

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

Upvotes: 0

Views: 191

Answers (1)

luksch
luksch

Reputation: 11712

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)

Upvotes: 2

Related Questions