CODEWITHSUNDEEP
mysqlsqlsyntaxsql-insertupsert
test
test

Reputation: 127

Add Value On Duplicate Key Update

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

Upvotes: 3

Views: 2862

Answers (1)

Mureinik
Mureinik

Reputation: 311163

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)

Upvotes: 6

Related Questions