在BigQuery中使用含FOR语句的SQL删除行时遇语法错误求助

BigQuery的DELETE语句不支持直接在目标表上使用FOR SYSTEM_TIME AS OF语法，因为快照是只读的，无法直接对其执行删除操作。你需要先从快照中提取目标行的唯一标识，再通过这些标识删除当前表中的对应行，具体步骤如下：

步骤1：从快照中获取目标行的唯一标识 #

先执行查询获取需要删除行的唯一键（可以是单一主键，也可以是多列组合的唯一标识）：

-- 替换id为你的表的唯一键列，若为多列则用逗号分隔，比如id, order_no
SELECT id
FROM `project.dataset.table` FOR SYSTEM_TIME AS OF TIMESTAMP("2022-08-30 11:10:00 UTC")
WHERE country = "US"
  AND SUBSTRING(request_start_date, 0,4) != CAST(year AS string)

步骤2：通过唯一标识删除当前表中的行 #

根据唯一标识的类型，选择以下两种方式之一执行删除：

方式一：使用IN子查询（适合单一主键） #

DELETE FROM `project.dataset.table`
WHERE id IN (
  SELECT id
  FROM `project.dataset.table` FOR SYSTEM_TIME AS OF TIMESTAMP("2022-08-30 11:10:00 UTC")
  WHERE country = "US"
    AND SUBSTRING(request_start_date, 0,4) != CAST(year AS string)
)

方式二：使用JOIN（适合多列组合唯一键） #

DELETE t
FROM `project.dataset.table` t
JOIN (
  SELECT id, order_no, user_id -- 替换为你的多列唯一标识
  FROM `project.dataset.table` FOR SYSTEM_TIME AS OF TIMESTAMP("2022-08-30 11:10:00 UTC")
  WHERE country = "US"
    AND SUBSTRING(request_start_date, 0,4) != CAST(year AS string)
) s
ON t.id = s.id 
AND t.order_no = s.order_no 
AND t.user_id = s.user_id -- 匹配所有唯一标识列

注意事项 #

• 务必确保用来匹配的列能唯一标识每一行，否则会误删无关数据
• 执行删除前，可将DELETE替换为SELECT t.*来预览要删除的行，确认无误后再执行删除
• 如果快照中的行在当前表中已被删除，该操作不会产生任何影响

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