CODEWITHSUNDEEP
mysqlsql
Addev
Addev

Reputation: 32221

On duplicate key update with the new value

I have two tables (bold=PK):

log: [id,type, value]

totals: [type,total]

Where the "total" value at "totals" is the sum of the values of all entries in the log with the same type. I want to update the totals table so I run a query:

INSERT INTO totals (type,total) (SELECT type,sum(value) AS total FROM log GROUP BY type)

This works fine except when the PK already exists, I'm trying to solve it adding a:

 ON DUPLICATE KEY UPDATE total=XXXXXXXX

but I don't know what to put as XXXXXXXXX to refer the new value

Upvotes: 3

Views: 1704

Answers (3)

YomY
YomY

Reputation: 613

As Gordon already said, you use VALUES(field) to get the value that would be inserted, or just field to get the old value

INSERT INTO .... ON DUPLICATE KEY UPDATE `field` = VALUES(`field`)

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

In your case - the complete query should look something like:

INSERT INTO totals (type,total) (SELECT type,sum(value) AS total FROM log GROUP BY type) ON DUPLICATE KEY UPDATE total = VALUES(total)

Upvotes: 2

J33nn
J33nn

Reputation: 3234

I'm not sure if I understand what do you want but:

ON DUPLICATE KEY UPDATE total = (SELECT COUNT(*) FROM log l WHERE l.type = type)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You want:

ON DUPLICATE KEY UPDATE total = VALUES(total)

Upvotes: 5

Related Questions