BigQuery合并使用显式值

小编典典

BigQuery合并使用显式值

sql

我了解BigQuery支持合并两个表。当前，该INSERT操作允许将显式值插入表中，例如

INSERT dataset.Inventory (product, quantity)
VALUES('top load washer', 10),
      ('front load washer', 20),
      ('dryer', 30),
      ('refrigerator', 10),
      ('microwave', 20),
      ('dishwasher', 30),
      ('oven', 5)

有没有办法做类似的事情MERGE？例如，类似：

MERGE dataset.DetailedInventory T
USING('top load washer', 10),
      ('front load washer', 20),
      ('dryer', 30),
      ('refrigerator', 10),
      ('microwave', 20),
      ('dishwasher', 30),
      ('oven', 5)
ON T.appliance = [I don't know what would go here]
WHEN NOT MATCHED THEN [insert]
WHEN MATCHED THEN [update]

我对GBQ和SQL相对较新，所以如果这是一个简单的问题，我深表歉意。

阅读 112

2021-04-15

共1个答案

小编典典

您可以使用子查询：

MERGE dataset.DetailedInventory T
USING (SELECT 'top load washer' as col1, 10 as col1 UNION ALL
       SELECT 'front load washer', 20 UNION ALL
       SELECT 'dryer', 30 UNION ALL
       SELECT 'refrigerator', 10 UNION ALL
       SELECT 'microwave', 20 UNION ALL
       SELECT 'dishwasher', 30 UNION ALL
       SELECT 'oven', 5
      ) src
ON T.appliance = src.col1
WHEN NOT MATCHED THEN [insert]
WHEN MATCHED THEN [update]

2021-04-15