CODEWITHSUNDEEP
sqlgoogle-bigquery
André Cordeiro
André Cordeiro

Reputation: 37

BigQuery - Delete based on values from another table

I'm trying to delete rows from one table, based on the second table using INNER JOIN on BigQuery, but it keeps giving error.

The context is simple, I have an input table that I'm fetching the dates contained there, and on the historic table, I want to delete those dates in order to put them after the delete.

The code that I have now is the following, however it's returning the error

Syntax error: Expected end of input but got keyword INNER at [3:1]

DELETE
FROM `datapool-prt-supplychain-wrk.TRANSPORTS_DATA.tbl_historico_tb_rota` AS a 
INNER JOIN
(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 ON b.data = a.data
WHERE b.data IS NOT NULL

Upvotes: 2

Views: 6023

Answers (1)

James
James

Reputation: 3015

Try to move your join logic to an exists clause

delete from `datapool-prt-supplychain-wrk.TRANSPORTS_DATA.tbl_historico_tb_rota` AS a 
where exists (
    select 1 
    from `datapool-prt-supplychain-wrk.TRANSPORTS_DATA.tbl_input_tb_rota` b
    where
      CAST(  
        CONCAT(
          RIGHT(b.data,4),'-',
          CASE 
            WHEN LENGTH(b.data) = 8 THEN 
              CONCAT('0',SUBSTR(b.data ,3,1),'-0',LEFT(b.data ,1))
            WHEN LENGTH(b.data) = 9 AND SUBSTR(b.data,3,1) = "-" THEN 
              CONCAT('0',SUBSTR(b.data ,4,1),"-",LEFT(b.data ,2))
            WHEN LENGTH(b.data) = 9 THEN 
              CONCAT(SUBSTR(b.data ,3,2),'-0',LEFT(b.data ,1))
            WHEN LENGTH(b.data) = 10 THEN 
              CONCAT(SUBSTR(b.data,4,2),'-',LEFT(b.data,2))
            ELSE '1900-01-01'
          END
        )
      AS DATE ) = a.data
      )

Upvotes: 3

Related Questions