BigQuery合并使用显式值 [英] BigQuery Merge Using Explicit Values

问题描述

我知道BigQuery支持合并两个表.当前， INSERT 操作允许将显式值插入表中，例如

I understand that BigQuery supports merging two tables. Currently, the INSERT operation allows inserting explicit values into a table, such as

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 操作执行类似的操作?例如，类似:

Is there a way to do something similar with the MERGE operation? For example, something like:

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相对较新，所以很抱歉，这是一个简单的问题.

I'm relatively new to GBQ and SQL, so my apologies if this is a simple question to answer.

推荐答案

您可以使用子查询:

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]

这篇关于BigQuery合并使用显式值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！