Update multiple rows with known keys without inserting new rows if nonexistent keys are found(如果找到不存在的键,则使用已知键更新多行而不插入新行)
问题描述
假设我们有表格items...
table: items
item_id INT PRIMARY AUTO_INCREMENT
title VARCHAR(255)
views INT
让我们想象它充满了类似的东西
Let's imagine that it is filled with something like
(1, item-1, 10),
(2, item-2, 10),
(3, item-3, 15)
我想根据从这个数组 [item_id] => [views] 获取的数据为这些项目创建 multi 更新视图
I want to make multi update view for this items from data taken from this array [item_id] => [views]
'1' => '50',
'2' => '60',
'3' => '70',
'5' => '10'
重要!请注意,我们在数组中有 item_id=5,但在数据库中没有 item_id=5.
IMPORTANT! Please note that we have item_id=5 in array, but we don't have item_id=5 in database.
我可以使用 INSERT ... ON DUPLICATE KEY UPDATE,但这样 image_id=5 将被插入到 talbe 项中.如何避免插入新密钥?我只想跳过 item_id=5,因为它不在表中.
I can use INSERT ... ON DUPLICATE KEY UPDATE, but this way image_id=5 will be inserted into talbe items. How to avoid inserting new key? I just want item_id=5 be skipped because it is not in table.
当然,在执行之前,我可以从项目表中选择现有的键;然后与数组中的键进行比较;删除不存在的密钥并执行 INSERT ... ON DUPLICATE KEY UPDATE.但也许有一些更优雅的解决方案?
Of course, before execution I can select existing keys from items table; then compare with keys in array; delete nonexistent keys and perform INSERT ... ON DUPLICATE KEY UPDATE. But maybe there is some more elegant solutions?
谢谢.
推荐答案
您可以尝试生成一个文字表并通过加入该表来更新项目:
You may try to generate a table of literals and update items by joining with the table:
UPDATE items
JOIN (SELECT 1 as item_id, 50 as views
UNION ALL
SELECT 2 as item_id, 60 as views
UNION ALL
SELECT 3 as item_id, 70 as views
UNION ALL
SELECT 5 as item_id, 10 as views
) as updates
USING(item_id)
SET items.views = updates.views;
这篇关于如果找到不存在的键,则使用已知键更新多行而不插入新行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!
本文标题为:如果找到不存在的键,则使用已知键更新多行而不插入新行


基础教程推荐
- CHECKSUM 和 CHECKSUM_AGG:算法是什么? 2021-01-01
- while 在触发器内循环以遍历 sql 中表的所有列 2022-01-01
- 如何在 CakePHP 3 中实现 INSERT ON DUPLICATE KEY UPDATE aka upsert? 2021-01-01
- ORA-01830:日期格式图片在转换整个输入字符串之前结束/选择日期查询的总和 2021-01-01
- MySQL 5.7参照时间戳生成日期列 2022-01-01
- MySQL根据从其他列分组的值,对两列之间的值进行求和 2022-01-01
- 使用 VBS 和注册表来确定安装了哪个版本和 32 位 2021-01-01
- 带有WHERE子句的LAG()函数 2022-01-01
- 从字符串 TSQL 中获取数字 2021-01-01
- 带更新的 sqlite CTE 2022-01-01