重复键更新-添加到现有值 [英] ON DUPLICATE KEY UPDATE - add to existing value

问题描述

我有类似SQL(MySQL 5.x)的查询:

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

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

这很好用.

现在我需要用VALUES(val1)+ ruby​​变量的总和来更新它.

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}

向我抛出错误.

(Ruby仅是一个示例，实际上我需要对VALUES(val1)+整数求和)

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

怎么办?

推荐答案

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;

所以您正在寻找这个:

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

x是您要插入的内容，ruby_variable是要添加到val1中的内容(如果有重复项).您需要VALUES进行插入，而不是ON DUPLICATE.

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.

这篇关于重复键更新-添加到现有值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！