CODEWITHSUNDEEP
sqlgoogle-bigquery
Jim Ott
Jim Ott

Reputation: 915

How to update table while joining on CTE results in BigQuery?

I think it will be obvious to see what I'm trying to do through this simplified example (works in PostgreSQL)....

with a as
(
    select 1 as id, 123.456 as value
)
update 
    mytable 
    set value = coalesce(a1.value, a2.value) 
from 
    a as a1, 
    a as a2 
where 
    a1.id = mytable.id 
or 
    a2.id = mytable.id2

This is a simplified example. In reality the "a" expression is pretty complex and I need to join to it multiple times in the update expression. Is there a way to do this in one statement in BigQuery?

Right now, the only option I see is to first create "a" as a table in a separate query, and then perform the update. That can work, but this is something that needs to run frequently on a schedule so it's not optimal.

Upvotes: 5

Views: 9329

Answers (2)

Yun Zhang
Yun Zhang

Reputation: 5518

With BigQuery scripting (Beta now), CREATE TEMP TABLE can be an alternative way. See public documentation here.

You should test to see the performance impact of using a temp table, which may be better or worse. As you can image, with scripting, it is possible to put multiple UPDATE query in one script and sharing a temp table a among them, which increases the opportunity of having better performance.

CREATE TEMP TABLE a as
    select 1 as id, 123.456 as value;

update 
    mytable 
    set value = coalesce(a1.value, a2.value) 
from 
    a as a1, 
    a as a2 
where 
    a1.id = mytable.id 
or 
    a2.id = mytable.id2

Upvotes: 3

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

You can transform you update statement into below 

#standardSQL
UPDATE 
  `project.dataset.mytable` mytable 
  SET value = new_value 
FROM (
  WITH a AS
  (
    SELECT 1 AS id, 123.456 AS value
  )  
  SELECT a1.id a1_id, a2.id a2_id, 
    COALESCE(a1.value, a2.value) new_value
  FROM 
    a AS a1, 
    a AS a2 
)
WHERE 
  a1_id = mytable.id 
OR 
  a2_id = mytable.id2

Upvotes: 7

Related Questions