Google Bigquery基于WHERE的多个更新 - 需要一个解决方案 [英] Google Bigquery multiple updates based on WHERE - need a solution

问题描述

是否有一种方法可以根据其他字段值进行多个更新
WHERE，而不是CASE

想法低于$ / b

谢谢

  #standardSQL 
 UPDATE dataset.people 
 SET CBSA_CODE =' 54620'where substr（zip，1,5）='99047'，
 SET CBSA_CODE ='31793'其中substr（zip，1,5）='45700'
  

解决方案

实际上， CASE 你会处理这个逻辑：

pre $ UPDATE dataset.people
SET CBSA_CODE = CASE SUBSTR（zip，1,5）
当 '99047' THEN '54620'
当 '45700'，然后 '31793' END
，其中
SUBSTR（拉链，1,5）IN（ '99047'，45700 ）;

我可以看到的唯一替代方案是运行多重更新语句，每个ZIP代码值。但与使用 CASE 表达式相比，这似乎很笨拙和不受欢迎。

is there a way to do multiple updates based on other field value WHERE, not CASE

idea is below

thanks

#standardSQL
UPDATE dataset.people
SET CBSA_CODE = '54620' where substr(zip,1,5) = '99047',
SET CBSA_CODE = '31793' where substr(zip,1,5) = '45700'

解决方案

A CASE expression is in fact the typical way you would handle this logic:

UPDATE dataset.people
SET CBSA_CODE = CASE SUBSTR(zip, 1, 5)
                    WHEN '99047' THEN '54620'
                    WHEN '45700' THEN '31793' END
WHERE
    SUBSTR(zip, 1, 5) IN ('99047', '45700');

The only alternative to this which I can see would be to run mutliple update statements, one for each ZIP code value. But that seems unwieldy and undesirable as compared to using a CASE expression.

这篇关于Google Bigquery基于WHERE的多个更新 - 需要一个解决方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！