如果在更新多行时尚不存在行，如何插入行？
我有一个看起来像这样的 MySQL 查询：
UPDATE `Table` SET `Column` =
CASE
WHEN `Option Id` = '1' THEN 'Apple'
WHEN `Option Id` = '2' THEN 'Banana'
WHEN `Option Id` = '3' THEN 'Q-Tip'
END
我的表当前看起来像这样：
Option Id | Column
1 | x
2 | x
我希望它的结果是：
Option Id | Column
1 | Apple
2 | Banana
3 | Q-Tip
但它不会插入 Q-Tip 行。我查阅并阅读了一些有关 INSERT ON DUPLICATE KEY UPDATE 和 REPLACE 的内容，但我找不到一种方法让它们能够处理此多行更新使用CASE。我是否必须为每一行编写一个单独的查询才能使其工作，或者在 MySQL 中是否有一个很好的方法来做到这一点？
Option Id 本身不是Key，但它是Primary Key 的一部分。
编辑更多信息：
我正在使用 PHP 进行编程，本质上我正在为用户存储一个数组。 Option Id 是键，Column 是值。因此，为了简单起见，我的表可能如下所示：
User Id | Option Id | Value
10 | 1 | Apple
10 | 2 | Shoe
11 | 1 | Czar
...
该用户可以轻松更新数组中的元素并添加新元素，然后将数组POST到服务器，在这种情况下我想存储它在表中。我上面的查询会更新他们编辑过的任何数组元素，但不会插入新元素。我想知道是否有一个查询可以从 POST 获取我的数组并将其插入表中，而无需我编写循环并对每个数组元素进行查询。
I have a MySQL query that looks like this:
UPDATE `Table` SET `Column` =
CASE
WHEN `Option Id` = '1' THEN 'Apple'
WHEN `Option Id` = '2' THEN 'Banana'
WHEN `Option Id` = '3' THEN 'Q-Tip'
END
An my table currently looks like this:
Option Id | Column
1 | x
2 | x
I'd like it to result in:
Option Id | Column
1 | Apple
2 | Banana
3 | Q-Tip
But it doesn't insert the Q-Tip row. I've looked up and read a bit about INSERT ON DUPLICATE KEY UPDATE and REPLACE, but I can't find a way to get those to work with this multiple row update using CASE. Do I have to write a separate query for each row to get this to work, or is there a nice way to do this in MySQL?
Option Id is not a Key itself, but it is part of the Primary Key.
EDIT Some more info:
I'm programming in PHP, and essentially I'm storing an array for the user. Option Id is the key, and Column is the value. So for simplicities sake, my table could look like:
User Id | Option Id | Value
10 | 1 | Apple
10 | 2 | Shoe
11 | 1 | Czar
...
That user can easily update the elements in the array and add new ones, then POST the array to the server, in which case I'd like to store it in the table. My query above updates any array elements that they've edited, but it doesn't insert the new ones. I'm wondering if there is a query that can take my array from POST and insert it into the table without me having to write a loop and have a query for every array element.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
如果 Option_Id 是主键，这应该可以工作：
该语句的意思是：如果 PK 已存在，则插入给定的行或替换值。
This should work, if
Option_Idis a primary key:The statement means: Insert the given rows or replace the values, if the PK is already existing.
当然不插入。由于没有这样的值，因此无法更新。
我想您正在通过输入已经存在的值来标准化数据库，现在想要为每个有效值添加所需的映射。
因此，最好从头开始，只进行 INSERT。
Of course it does not insert. As there is no such value, it cannot get updated.
I suppose you are normalizing a database by putting in the values already present and now want to add the required mapping for every valid value.
So it would be better to start from scratch and just do INSERTs.
您始终可以查询数据库中的条目，然后根据结果选择更新或插入
You could always query the database for entries and then choose update or insert based on yor results
我认为这样的更新没有意义。
为什么不使用一个单独的表来包含选项 id 和相应的值，只留下链接到用户 id 的选项 id 呢？
I see no point in such updating.
Why don't you have a separate table with option ids and corresponding values, leaving only option ids linked to user ids in this one?