How do I update a table with old value + the new value?

Question

I have this SQL string which updates a row if it exists or creates a row if it does not, how do I do if it updates ctc_portfolio_coins_amount

If I have a value of 100, or -100, how do I either substract it or add it to the current value when it updates?

INSERT INTO ctc_portfolio_coins
    (ctc_portfolio_coins_portfolio_fk, ctc_portfolio_coins_coin_fk, ctc_portfolio_coins_amount)
    VALUES (1, 1, 100)
    ON DUPLICATE KEY UPDATE ctc_portfolio_coins_amount = VALUES(ctc_portfolio_coins_amount)

Table:

Possible duplicate of [Insert into a MySQL table or update if exists](https://stackoverflow.com/questions/4205181/insert-into-a-mysql-table-or-update-if-exists) — miken32, Sep 19 '17 at 23:23

score 2 · Accepted Answer · answered Sep 17 '17 at 14:13

Arithmetic:

INSERT INTO ctc_portfolio_coins (ctc_portfolio_coins_portfolio_fk, ctc_portfolio_coins_coin_fk, ctc_portfolio_coins_amount)
    VALUES (1, 1, 100)
    ON DUPLICATE KEY UPDATE
        ctc_portfolio_coins_amount = ctc_portfolio_coins_amount + VALUES(ctc_portfolio_coins_amount);

The reference to ctc_portfolio_coins_amount is the value in the column before the update. The reference to VALUES(ctc_portfolio_coins_amount) is the value passed into the INSERT statement.

Thats very nice i tried something similair just inside the `value()` thanks for the help — ii iml0sto1, Sep 17 '17 at 14:16

How do I update a table with old value + the new value?

1 Answers1