时间戳

首页 > 解决方案 > 使用 LIKE 的 bigquery 更新表返回“UPDATE/MERGE 必须与每个目标行最多匹配一个源行”

sql - 使用 LIKE 的 bigquery 更新表返回“UPDATE/MERGE 必须与每个目标行最多匹配一个源行”

问题描述

我有两个表，并希望使用 LIKE 语句使用在 table2（映射表）中找到的数据更新 table1（原始数据表）。但是，对于所有不同的尝试，我总是收到错误消息：

查询错误：UPDATE/MERGE 必须与每个目标行最多匹配一个源行

表1（数据表）

textWithFoundItemInIt         | foundItem
---------------------------------
hallo Adam                    |  
Bert says hello               | 
Want to find "Caesar"bdjehg   |

表2（映射表）

mappingItem
------------
Adam
Bert
Caesar

预期结果

textWithFoundItemInIt         | foundItem
---------------------------------
hallo Adam                    |  Adam
Bert says hello               |  Bert
Want to find "Caesar"bdjehg   |  Caesar

查询：

UPDATE `table1`
SET foundItem= mt.mappingItem
    FROM `mappingTable` mt
    WHERE textWithFoundItemInIt LIKE CONCAT('%', mt.mappingItem, '%');


UPDATE `table1`
SET foundItem= mt.mappingItem
    FROM `mappingTable` mt
     WHERE INSTR(textWithFoundItemInIt , mt.mappingItem) >1;


UPDATE `table1`
SET foundItem = (SELECT mt.mappingItem FROM `table2` AS mt
WHERE textWithFoundItemInIt LIKE CONCAT('%', mt.mappingItem, '%')
)
WHERE TRUE; 


UPDATE `table1`
SET foundItem= mt.mappingItem
FROM `table1`
inner join  `table2` mt on textWithFoundItemInIt LIKE CONCAT('%', mt.mappingItem, '%');

我还删除了表 1 和表 2 中的所有重复值，但仍然有相同的错误消息。我也尝试使用连接语句，但我得到了这个错误消息：“FROM 子句中的别名 table1 已定义为 UPDATE 目标”

我在 SO 中发现了这些类似的问题，并尝试使用他们的方法：

不幸的是，它们对解决我的问题没有帮助。所以我认为这不是一个重复的问题。

非常感谢您的想法。

跟进问题

我指的是@Jon 发布的解决方案。再次感谢您的帮助。但是，在用不同的数据进行测试后，仍然存在如果'table1'中有重复则它不起作用的问题。当然这个问题来自'GROUP BY'语句 - 如果没有这个，UPDATE查询不起作用，导致我原来的问题中所述的错误消息。如果我对每个值进行分组，它也不起作用。

但是，我的 'table1' (Data) 和我的映射表 'table2' 中可能有重复项。因此，为了使其非常精确，这是我的目标：

表1（数据表）

textWithFoundItemInIt         | foundItem
-------------------------------------------
hallo Adam                    |  
Bert says hello               | 
Bert says byebye              | 
Want to find "Caesar"bdjehg   |
Want to find "Caesar"bdjehg   |
Want to find "Caesar"again    |
Want to find "CaesarCaesar"again and again | <== This is no problem, just finding one Caesar is enough

表2（映射表）

mappingItem
------------
Adam
Bert
Caesar
Bert
Caesar
Adam

预期结果

textWithFoundItemInIt         | foundItem
--------------------------------------------
hallo Adam                    |  Adam
Bert says hello               |  Bert
Bert says byebye              |  Bert
Want to find "Caesar"bdjehg   |  Caesar
Want to find "Caesar"bdjehg   |  Caesar
Want to find "Caesar"again    |  Caesar
Want to find "CaesarCaesar"again and again | Caesar

找到 Table2 中的哪个 Adam 并将其插入 Table1 无关紧要，它们将是相同的。因此，如果第一个 Adam 将被第二个 Adam 覆盖，或者一旦找到一个 Adam，查询就停止进一步搜索，这也是可以的。

如果我执行 Jon 的 'SELECT' 查询，它将导致：

textWithFoundItemInIt         | foundItem
--------------------------------------------
hallo Adam                    |  Adam
Bert says hello               |  Bert
Bert says byebye              |  Bert
Want to find "Caesar"bdjehg   |  Caesar
Want to find "Caesar"again    |  Caesar
Want to find "CaesarCaesar"again and again | Caesar

它（正确地）省略了第二个“想再次找到“凯撒”，但不幸的是，这不是我需要的。

如果更容易，也可以在一行中找到两个名称的情况下

textWithFoundItemInIt         | foundItem
---------------------------------------------
hallo Adam and Bert           |  Adam, Bert 
Bert says hello to Caesar     |  Bert, Caesar

或者

textWithFoundItemInIt         | foundItem1      | foundItem2
---------------------------------------------------------------
hallo Adam and Bert           |  Adam           | Bert 
Bert says hello to Caesar     |  Bert           | Caesar

我希望这有助于理解我的问题。简而言之：“这只是具有多个相等行的映射”;-)

非常感谢 ：）

标签： sqlgoogle-bigquerymappingsql-like

解决方案

您的逻辑不能防止这种情况：

mappingItem
-----------
item1
item12

由于模式%item1%将同时匹配item1和item12。有很多方法可以避免这种情况，这取决于您希望如何在结构不良的数据中处理这些问题。但这就是原因。

您可以通过以下方式查找问题：

SELECT table1.textWithFoundItemInIt
     , COUNT(*)
  FROM table1
  JOIN table2
    ON table1.textWithFoundItemInIt LIKE CONCAT('%', table2.mappingItem, '%')
 GROUP BY table1.textWithFoundItemInIt 
HAVING COUNT(*) > 1

一旦您决定如何处理这些情况，您应该能够选择匹配选项中的哪一个在UPDATE.

基本上，确保逻辑将要分配的值列表（每 table1 行）限制为一 (1) 个值。

这是一种方法。我不确定 bigquery 是否支持这种特定形式。但它显示了一种合乎逻辑的方法。

查看数据，注意我们有一个以上mappingItem匹配table1一行的情况：

SELECT table1.textWithFoundItemInIt
     , COUNT(*)
     , MIN(table2.mappingItem) AS theItem1
     , MAX(table2.mappingItem) AS theItem2
  FROM table1
  JOIN table2
    ON table1.textWithFoundItemInIt LIKE CONCAT('%', table2.mappingItem, '%')
 GROUP BY table1.textWithFoundItemInIt 
HAVING COUNT(*) > 1
;

+-----------------------+----------+----------+----------+
| textWithFoundItemInIt | COUNT(*) | theItem1 | theItem2 |
+-----------------------+----------+----------+----------+
| Item12 is a problem   |        2 | item1    | item12   |
+-----------------------+----------+----------+----------+

现在调整以在分配新值时UPDATE选择MIN(mappingItem)每行：table1

UPDATE table1
  JOIN ( SELECT textWithFoundItemInIt
              , MIN(mappingItem) AS mappingItem
           FROM table1
           JOIN table2
             ON table1.textWithFoundItemInIt LIKE CONCAT('%', table2.mappingItem, '%')
          GROUP BY table1.textWithFoundItemInIt 
       ) mt
    ON table1.textWithFoundItemInIt = mt.textWithFoundItemInIt 
   SET foundItem = mt.mappingItem
;

查看结果：

SELECT * FROM table1;

+----------------------------+-----------+
| textWithFoundItemInIt      | foundItem |
+----------------------------+-----------+
| hallo Item1                | item1     |
| Item2 says hello           | item2     |
| Item12 is a problem        | item1     |
| Want to find "Item3"bdjehg | item3     |
+----------------------------+-----------+

注意：这会根据原始请求更新所有目标行，甚至是问题行。这可以调整为仅触摸那些还没有foundItem集合的行，WHERE foundItem IS NULL.

推荐阅读