Add CASE statement column to existing GBQ table-CodePudding

Pretend I have a GBQ table:

SELECT 
'city_A' AS city,
15 AS score
UNION ALL
SELECT 
'city_A' AS city,
17 AS score
UNION ALL
SELECT 
'city_B' AS city,
8 AS score

I want to add new column with CASE statement, like:

CASE 
    WHEN 'city_A' THEN'LA'
    WHEN 'city_B' THEN 'PH' 
    ELSE NULL END AS iso_code

How to add/generate it ? With INSERT I only can insert blank column specifying all constraints, ain't I ?

CodePudding user response：

You want an update here. Assuming your table already have an iso_code column, then use:

UPDATE yourTable
SET iso_code = CASE city WHEN 'city_a' THEN 'LA'
                         WHEN 'city_b' THEN 'PH' END
WHERE city IN ('city_a', 'city_b');

That being said, your iso_code column really feels like it could be a generated column, if BigQuery would support that.