如何在重复键上更新多个列

I have a series of radiobuttons that are inserted into a database in an array. This works fine the first time through.

我有一系列的radiobutton，它们被插入到一个数组中的数据库中。这在第一次使用时效果很好。

$query="INSERT INTO `notes_value` ( `style_color`, `note_id`, `value`, `created_at` ) VALUES ";
        foreach ($params AS $key => $value) {       
            if (( $key != "form_key" ) && ( $key != "stylecolor" ) && ( $key != "key" )){           
                $values[] = "('$sku', '$key', '$value', NOW()) ";       
                }
            }
        $query .= implode(', ', $values) . ';';

I am trying to get it to update these columns when the state of the radio button is changed so changing this :

当单选按钮的状态发生变化时，我试图让它更新这些列，所以改变如下:

$values[] = "('$sku', '$key', '$value', NOW()) ON DUPLICATE KEY UPDATE value = '$value'";

It will work and update if there is only one radio button in the array selected with this query:

如果在这个查询中选择的数组中只有一个单选按钮，它将工作和更新:

 INSERT INTO `notes_value` ( `style_color`, `note_id`, `value`, `created_at` )
 VALUES ('1893240720', '1', 'no', NOW()) 
 ON DUPLICATE KEY UPDATE value = 'yes';

however when I have multiple buttons selected it breaks the query :

但是当我选择了多个按钮时，它会中断查询:

INSERT INTO `notes_value` ( `style_color`, `note_id`, `value`, `created_at` )
VALUES ('1893300667', '1', 'yes', NOW())
ON DUPLICATE KEY UPDATE value = 'no',
('1893300667', '2', 'yes', NOW()) 
ON DUPLICATE KEY UPDATE value = 'maybe';

Do I have to have separate INSERT or how do I check for duplicates and then update each value of its respective note_id ? Is this just a case of bad syntax?

我是否必须有单独的插入，或者如何检查副本，然后更新各自的note_id的每个值?这只是语法错误吗?

edit

from the answers so far it seems I was not clear that I need to be able to update multiple values at once. I dont understand how I can have the ON DUPLICATE at the end if I need to update multiple values with different variables.

从目前的答案来看，我似乎不清楚是否需要同时更新多个值。如果我需要用不同的变量更新多个值，我不知道如何在最后得到ON DUPLICATE。

3 个解决方案

#1

Zac, yes you can perform this action by specifying the statement once (at the end):

Zac，是的，你可以通过指定语句一次来执行这个动作(在最后):

$query = "INSERT INTO `notes_value` ( `style_color`, `note_id`, `value`, `created_at` ) VALUES ";

foreach ($params AS $key => $value) {       
    if (( $key != "form_key" ) && ( $key != "stylecolor" ) && ( $key != "key" )){           
        $values[] = "('$sku', '$key', '$value', NOW()) ";       
    }
}
$query .= implode(', ', $values) . 'ON DUPLICATE KEY UPDATE value = VALUES(value);';

See:

看到的:

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

#2

If your primary key is note_id, then

如果您的主键是note_id，那么

INSERT INTO `notes_value` ( `style_color`, `note_id`, `value`, `created_at` )
VALUES ('1893300667', '1', 'yes', NOW())
ON DUPLICATE KEY UPDATE
`value` = 'no',
style_color = VALUES(style_color),
created_at = VALUES(created_at);

#3

I beleive you only specify the ON DUPLICATE KEY UPDATE once. Like this..

我相信您只指定一次ON DUPLICATE KEY UPDATE。像这样的. .

INSERT INTO `notes_value` ( `style_color`, `note_id`, `value`, `created_at` ) 
VALUES ('1893300667', '1', 'yes', NOW()), 
       ('1893300667', '2', 'yes', NOW()) 
ON DUPLICATE KEY UPDATE value = values(`value`);

EDIT: Forgot the first NOW(), which I've added. EDIT: Added Value Function to pull in value of column.

编辑:忘记了第一个NOW()，这是我添加的。编辑:添加值函数，拉入列的值。

It's been awhile since I've done this, but I believe this is the case.

我做这件事已经有一段时间了，但我相信情况就是这样。

#1