CODEWITHSUNDEEP
sqlgoogle-bigquerysql-updatecommon-table-expression
Rosscoasks
Rosscoasks

Reputation: 107

BigQuery UPDATE statement with CTEs

Looking for some help with BigQuery.

I can't seem to use CTEs in UPDATE statements, i.e:

with ctename as
(select
    column1,
    column2,
    column3,
from blah)


update table2 
set table2.column2 = ctename.column2
from table2
inner join ctename
    on ctename.column1 = table2.column1

I can't find any reason as to why this shouldn't work in BigQuery. Any help/advice would be greatly appreciated.

Upvotes: 1

Views: 3399

Answers (3)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

Looks like below is equivalent of what you are trying to achieve, while technically preserving your cte's query (that obviously can be much much more complex than just select * from the blah)

update table2 
set column2 = ctename.column2
from (
  select column1, column2, column3
  from blah
) ctename 
where ctename.column1 = table2.column1

Note: as it is implicitly comes from your question and from other answer(s) - it is expected that that there is 1:1 matches by column1 - otherwise you will get error

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270401

I don't think BigQuery supports CTEs in updates. You can write this logic as:

update table2 
    set table2.column2 = (select blah.column2 from blah where blah.column1 = table2.column1)
    where exists (select blah.column2 from blah where blah.column1 = table2.column1);

Upvotes: 2

Popeye
Popeye

Reputation: 35920

You can try with SELECT query and JOIN in CTE as follows:

with cte as
(select
    blah.column1,
    blah.column2,
    blah.column3,
    table2.column2 as t2col2
from blah
inner join table2
on blah.column1 = table2.column1)
update cte 
set t2col2= column2;

Upvotes: -1

Related Questions