CODEWITHSUNDEEP
google-bigquerysql-update
mikestr
mikestr

Reputation: 33

Multiple SET - WHERE in the same UPDATE

I have a table in Bigquery in which I want to update several records, currently I do an UPDATE for each record:

UPDATE MY_DATASET.TABLE_ONE
SET fieldOne = 'data1', fieldTwo = 'data2'
WHERE fieldKey = 'key1' and fieldData in ('dataF1','dataF3')

UPDATE MY_DATASET.TABLE_ONE
SET fieldOne = 'data11', fieldTwo = 'data21'
WHERE fieldKey = 'key2' and fieldData = 'dataF2'

When making two UPDATEs on the same table, it is possible to make only one UPDATE but with several SET and WHERE? statements, that is, is it possible to join both UPDATE?

Upvotes: 0

Views: 2372

Answers (1)

Jaytiger
Jaytiger

Reputation: 12264

You can use CASE .. WHEN.. expression. Consider below query.

CREATE TEMP TABLE TABLE_ONE AS
SELECT 'key1' fieldKey, 'dataF1' fieldData, 'dummy1' fieldOne, 'dummy2' fieldTwo UNION ALL
SELECT 'key2' fieldKey, 'dataF2' fieldData, 'dummy1' fieldOne, 'dummy2' fieldTwo UNION ALL
SELECT 'key3' fieldKey, 'dataF3' fieldData, 'dummy1' fieldOne, 'dummy2' fieldTwo;

UPDATE TABLE_ONE
SET fieldOne = CASE (fieldKey, fieldData) WHEN ('key1', 'dataF1') THEN 'data1' ELSE 'data11' END,
    fieldTwo = CASE (fieldKey, fieldData) WHEN ('key1', 'dataF1') THEN 'data2' ELSE 'data21' END
WHERE (fieldKey, fieldData) IN (('key1', 'dataF1'), ('key2', 'dataF2'));

SELECT * FROM TABLE_ONE;
  • tuple syntax can be used with STRUCT comparison for comparison expressions using multi-part keys, e.g. in a WHERE clause: 
    WHERE (Key1,Key2) IN ( (12,34), (56,78) )

Query results

enter image description here

Update: you can think tuple syntax as kind of syntatic sugar. If you have more complicated conditions, you can write them down as they are both in WHERE and CASE conditions.

UPDATE TABLE_ONE
SET fieldOne = CASE WHEN fieldKey = 'key1' and fieldData in ('dataF1','dataF3') THEN 'data1' ELSE 'data11' END,
    fieldTwo = CASE WHEN fieldKey = 'key2' and fieldData = 'dataF2' THEN 'data2' ELSE 'data21' END
WHERE (fieldKey = 'key1' and fieldData in ('dataF1','dataF3'))
   OR (fieldKey = 'key2' and fieldData = 'dataF2');

Upvotes: 1

Related Questions