CODEWITHSUNDEEP
where-clauseupdatesgoogle-bigquery
Lev Savranskiy
Lev Savranskiy

Reputation: 430

Google Bigquery multiple updates based on WHERE - need a solution

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'

Upvotes: 0

Views: 7306

Answers (2)

filiprem
filiprem

Reputation: 7144

Use SQL CASE, which is part of Standard SQL (see official BQ docs):

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

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522181

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.

Upvotes: 2

Related Questions