Add Value On Duplicate Key Update

Question

I have a table with the schema:

storeId varchar(255),
ttl int,
date varchar(255),
bytes bigint(255),
UNIQUE KEY storeId_date_index (storeId, date)

I am wanting to insert a row if it doesn't exist, otherwise update it.

For each duplicate key I hit, I want to sum the old value with the new value. How is this possible with the ON DUPLICATE KEY UPDATE command?

Here is what I have so far:

insert into table (storeId, date, ttl, bytes) 
values 
('477866', '2019-02-05', 54543543, 100),
('6301', '2019-02-05', 54543543, 999999),
('12345', '2019-02-05', 54543543, 999999)
ON DUPLICATE KEY UPDATE
bytes = oldval + newval # sum old value with new value where storeId and date match in the values

Mureinik · Accepted Answer · 2022-07-21 08:56:09Z

6

To quote the documentation:

In assignment value expressions in the ON DUPLICATE KEY UPDATE clause, you can use the VALUES(col_name) function to refer to column values from the INSERT portion of the INSERT ... ON DUPLICATE KEY UPDATE statement.

I.e.:

INSERT INTO mhytable(storeId, date, ttl, bytes) 
VALUES 
('477866', '2019-02-05', 54543543, 100),
('6301', '2019-02-05', 54543543, 999999),
('12345', '2019-02-05', 54543543, 999999)
ON DUPLICATE KEY UPDATE
bytes = bytes + VALUES(bytes)

edited Jul 21, 2022 at 8:56

answered Feb 5, 2019 at 15:08

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Add Value On Duplicate Key Update

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related