BigQuery使用INNER JOIN基于另一表数据删除行报错问题咨询

嘿，我来帮你搞定这个BigQuery的DELETE语法问题！你遇到的报错是因为BigQuery的DELETE语句不支持直接在FROM子句后使用INNER JOIN，它的语法规范要求通过WHERE子句里的关联条件（比如EXISTS或IN）来匹配需要删除的行。

你的需求是从历史表中删除输入表转换后存在的日期行，我给你调整了两种可行的写法：

方案1：使用EXISTS子句关联删除 #

这种写法通过检查历史表的行是否在输入表转换后的日期集合中，来决定是否删除：

DELETE FROM `datapool-prt-supplychain-wrk.TRANSPORTS_DATA.tbl_historico_tb_rota` AS a
WHERE EXISTS (
  SELECT 1
  FROM (
    SELECT DISTINCT 
      CAST( 
        CONCAT(
          RIGHT(data,4),'-', 
          CASE 
            WHEN LENGTH(data) = 8 THEN CONCAT('0',SUBSTR(data ,3,1),'-0',LEFT(data ,1)) 
            WHEN LENGTH(data) = 9 AND SUBSTR(data,3,1) = "-" THEN CONCAT('0',SUBSTR(data ,4,1),"-",LEFT(data ,2)) 
            WHEN LENGTH(data) = 9 THEN CONCAT(SUBSTR(data ,3,2),'-0',LEFT(data ,1)) 
            WHEN LENGTH(data) = 10 THEN CONCAT(SUBSTR(data,4,2),'-',LEFT(data,2)) 
            ELSE '1900-01-01' 
          END 
        ) AS DATE 
      ) AS data 
    FROM `datapool-prt-supplychain-wrk.TRANSPORTS_DATA.tbl_input_tb_rota`
  ) AS b
  WHERE b.data = a.data 
    AND b.data IS NOT NULL
)

方案2：使用IN子句匹配删除 #

如果逻辑更简单，也可以直接用IN子句来匹配需要删除的日期：

DELETE FROM `datapool-prt-supplychain-wrk.TRANSPORTS_DATA.tbl_historico_tb_rota` AS a
WHERE a.data IN (
  SELECT DISTINCT 
    CAST( 
      CONCAT(
        RIGHT(data,4),'-', 
        CASE 
          WHEN LENGTH(data) = 8 THEN CONCAT('0',SUBSTR(data ,3,1),'-0',LEFT(data ,1)) 
          WHEN LENGTH(data) = 9 AND SUBSTR(data,3,1) = "-" THEN CONCAT('0',SUBSTR(data ,4,1),"-",LEFT(data ,2)) 
          WHEN LENGTH(data) = 9 THEN CONCAT(SUBSTR(data ,3,2),'-0',LEFT(data ,1)) 
          WHEN LENGTH(data) = 10 THEN CONCAT(SUBSTR(data,4,2),'-',LEFT(data,2)) 
          ELSE '1900-01-01' 
        END 
      ) AS DATE 
    ) AS data 
  FROM `datapool-prt-supplychain-wrk.TRANSPORTS_DATA.tbl_input_tb_rota`
  WHERE data IS NOT NULL
)

可选优化：用临时表简化逻辑 #

如果你的日期转换逻辑比较复杂，还可以先把转换后的日期存入临时表，再关联删除，这样代码可读性更高：

-- 先创建临时表存储转换后的有效日期
CREATE TEMP TABLE temp_target_dates AS
SELECT DISTINCT 
  CAST( 
    CONCAT(
      RIGHT(data,4),'-', 
      CASE 
        WHEN LENGTH(data) = 8 THEN CONCAT('0',SUBSTR(data ,3,1),'-0',LEFT(data ,1)) 
        WHEN LENGTH(data) = 9 AND SUBSTR(data,3,1) = "-" THEN CONCAT('0',SUBSTR(data ,4,1),"-",LEFT(data ,2)) 
        WHEN LENGTH(data) = 9 THEN CONCAT(SUBSTR(data ,3,2),'-0',LEFT(data ,1)) 
        WHEN LENGTH(data) = 10 THEN CONCAT(SUBSTR(data,4,2),'-',LEFT(data,2)) 
        ELSE '1900-01-01' 
      END 
    ) AS DATE 
  ) AS data 
FROM `datapool-prt-supplychain-wrk.TRANSPORTS_DATA.tbl_input_tb_rota`
WHERE data IS NOT NULL;

-- 从历史表删除匹配的行
DELETE FROM `datapool-prt-supplychain-wrk.TRANSPORTS_DATA.tbl_historico_tb_rota` AS a
WHERE EXISTS (
  SELECT 1 FROM temp_target_dates AS b WHERE b.data = a.data
);

内容的提问来源于stack exchange，提问作者André Cordeiro