IssueTracker

Will something like this be supported?

MERGE `targetdb.cool_table` T
      USING (SELECT * FROM `sourcedb.source149837843989`) S
      ON T.id = S.id
      WHEN MATCHED THEN
        DELETE; INSERT ROW 
      WHEN NOT MATCHED THEN
        INSERT ROW

...I guess if there is a different number of columns it will always be an error? so maybe this isn't the right solution for me :/ back to the drawing board

Hi, is it currently possible to update all values of row without having to write out every column? It gets pretty tedious to list out all columns after UPDATE SET, especially if I want to run this for multiple tables.

For INSERT it is possible to use ROW, but couldn't find the syntax to do this with UPDATE.

MERGE `target` T
USING `source` S
ON T.userId = S.userId
WHEN MATCHED AND (condition) THEN
  UPDATE SET
    col1=S.col1,
    col2=S.col2,
    col3=S.col3,
    col4=S.col4,
    col5=S.col5,
    col6=S.col6
WHEN NOT MATCHED THEN
  INSERT ROW

In reality I have >20 columns and had to write a Python script to generate the SQL code.

dml_query = """MERGE `target` T
USING `source` S
ON T.userId = S.userId
WHEN MATCHED AND (condition) THEN
  UPDATE SET
"""

for col in ["col1", "col2", "col3", "col4", "col5", "col6"]:
    dml_query += f"    {col}=S.{col},\n"
# remove last ,
dml_query = dml_query[:-2]

dml_query += """\nWHEN NOT MATCHED THEN
  INSERT ROW"""
print(dml_query)

Is it already possible, or we have to wait for this feature to be implemented as mentioned in #23?

Hey Stef, Unfortunately overwriting all rows within an UPDATE statement without explicitly listing them isn't possible. As such, your approach seems to be the best option today, especially if you have tables which occasionally change schema.

FYI the BigQuery change data capture (CDC) feature has now launched into public preview. You can learn more about it with these resources:

BigQuery CDC documentation

BigQuery CDC blog post