Is it possible to use SQL Update on a Sub Query-CodePudding

Is it possible to use the sql UPDATE on a sub query? I am using Big Query Standard SQL and have tried every permutation of the below that I can come up with:

WITH test AS (SELECT * FROM 'my.database.table'),
test2 AS (UPDATE test SET myField = 100 WHERE myField < 100)
SELECT * FROM test2

I always receive the error:

Syntax error: Expected "(" or keyword SELECT or keyword WITH but got keyword UPDATE

CodePudding user response：

Use below instead

with test as (
  select * from `my.database.table`
), test2 as (
  select * replace(greatest(myfield, 100) as myfield) from test 
)
select * from test2