如果发现不存在的键，则用已知键更新多行而无需插入新行 [英] Update multiple rows with known keys without inserting new rows if nonexistent keys are found

问题描述

让我们想象一下，我们有表项 ...

Let's imagine that we have table 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]

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;

这篇关于如果发现不存在的键，则用已知键更新多行而无需插入新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！