ON DUPLICATE KEY UPDATE - add to existing value

Question

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

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

And this works fine.

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

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

throws me an error.

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

How it could be done?

Answer 1

Right at the top of the fine manual you will see an example of exactly the sort of thing you're trying to do:

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

So you're 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're trying to insert and ruby_variable is what you want to add to val1 when there is a duplicate. You need a VALUES for the INSERT, not for the ON DUPLICATE.