CODEWITHSUNDEEP
google-bigquery
Sourav Roy
Sourav Roy

Reputation: 407

How can I update in bulk without running line by line each update statement for each group of conditions

sample data I have a table now I want to update the table where each line matches something like this

UPDATE internal.temorary_check SET monday = 1, tuesday =2 where ID_recipient = 23771085 and action date hour = 1; UPDATE internal.temorary_check SET monday = 4, tuesday =7 where ID_recipient = 23771085 and action date hour = 3;

UPDATE internal.temorary_check SET monday = 7, tuesday =1 where ID_recipient = 32243506 and action date hour = 1; and so on

Is there a way to do it in bulk or using array-like something

UPDATE internal.temorary_check SET monday = [4,7,3], tuesday =[2,7,1] where ID_recipient in (23771085,23771085,3224432) and action date hour in(3,1,2);

There will be 100 of groups by python and string concatenation I need to create a proper script.

Upvotes: 0

Views: 36

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173056

Below is for BigQuery Standard SQL 

#standardSQL
UPDATE `internal.temorary_check`
SET monday = set_value.mon, tuesday = set_value.tue
FROM (
  SELECT [
    STRUCT(STRUCT(23771085 AS id, 1 AS hour) AS lookup_value, STRUCT(1 AS mon, 2 AS tue) AS set_value),
    ((23771085, 3), (4, 7)),
    ((32243506, 1), (7, 1))
  ] x
) dict, UNNEST(x) y 
WHERE (ID_recipient, action_date_hour) = lookup_value

As you can see you can put all your different scenarios into dict and then use it as a lookup and set values. Obviously yo will need to expand set_value struct and list of dicts. You can also first create dict table with all those values in which case update statement will get down to very simple and manageable statement 

#standardSQL
UPDATE `internal.temorary_check`
SET monday = set_value.mon, tuesday = set_value.tue
FROM dict, UNNEST(x) y 
WHERE (ID_recipient, action_date_hour) = lookup_value

Upvotes: 2

Related Questions