BigQuery SQL语法错误排查：将day_of_week转为星期字符串失败

错误原因 #

你把SELECT查询和UPDATE更新语句写在同一个脚本中，BigQuery不支持在单个SQL语句里同时执行查询和更新操作，这直接导致了语法报错“Syntax error: Expected end of input but got identifier”。

正确解决方案 #

方案一：修改原表字段类型并更新值（直接修改原数据） #

需要分两步执行：

1. 先将day_of_week字段的整数类型改为字符串类型：

ALTER TABLE `my-data-project12345-368309.cyclistic_jamesbimler.2022_Q1`
ALTER COLUMN day_of_week SET DATA TYPE STRING;

2. 再执行UPDATE语句替换为星期名称：

UPDATE `my-data-project12345-368309.cyclistic_jamesbimler.2022_Q1`
SET day_of_week = 
    CASE 
      WHEN day_of_week = '1' THEN 'Sunday'
      WHEN day_of_week = '2' THEN 'Monday'
      WHEN day_of_week = '3' THEN 'Tuesday'
      WHEN day_of_week = '4' THEN 'Wednesday'
      WHEN day_of_week = '5' THEN 'Thursday'
      WHEN day_of_week = '6' THEN 'Friday'
      WHEN day_of_week = '7' THEN 'Saturday'
    END
WHERE day_of_week IN ('1','2','3','4','5','6','7');

方案二：创建新表存储转换后的数据（不修改原表，更安全） #

如果不想改动原始数据，可以直接通过查询生成包含转换后字段的新表：

CREATE OR REPLACE TABLE `my-data-project12345-368309.cyclistic_jamesbimler.2022_Q1_updated`
AS
SELECT
  ride_id,
  rideable_type,
  started_at,
  ended_at,
  start_station_name,
  start_station_id,
  end_station_name,
  end_station_id,
  start_lat,
  start_lng,
  end_lat,
  end_lng,
  member_casual,
  ride_length,
  ride_date,
  ride_month,
  ride_year,
  start_time,
  end_time,
  CASE 
    WHEN day_of_week = 1 THEN 'Sunday'
    WHEN day_of_week = 2 THEN 'Monday'
    WHEN day_of_week = 3 THEN 'Tuesday'
    WHEN day_of_week = 4 THEN 'Wednesday'
    WHEN day_of_week = 5 THEN 'Thursday'
    WHEN day_of_week = 6 THEN 'Friday'
    WHEN day_of_week = 7 THEN 'Saturday'
  END AS day_of_week,
  quarter
FROM `my-data-project12345-368309.cyclistic_jamesbimler.2022_Q1`;

注意：方案二中因为原字段是整数类型，所以CASE语句里直接用整数（WHEN day_of_week = 1）匹配即可，无需先转换为字符串。

内容的提问来源于stack exchange，提问作者bombfirst885