ON DUPLICATE KEY UPDATE - add to an existing value

Question

I have a SQL query (MySQL 5.x):

INSERT INTO table (val1), ON DUPLICATE KEY UPDATE `val1` = VALUES(`val1`) 

And it works great.

Now I need to update it with the sum of the variable VALUES ( val1 ) + ruby.

  INSERT INTO table (val1), ON DUPLICATE KEY UPDATE `val1` = VALUES(`val1`) + #{ruby_variable} 

causes an error.

(Ruby is just an example here, actually I need to summarize VALUES ( val1 ) + integer)

How can I do that?

Answer 1

At the top of the fine manual you will see an example of exactly what you are trying to do:

 INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1; 

So you are looking for this:

 connection.execute(%Q{ INSERT INTO table (val1) VALUES(#{connection.quote(x)}) ON DUPLICATE KEY UPDATE `val1` = `val1` + #{connection.quote(ruby_variable)} }) 

Where x is what you are trying to insert, and ruby_variable is what you want to add to val1 when there is a duplicate. You need VALUES for INSERT, not ON DUPLICATE.