[SQL] Efficient syntax to update 5K rows in BQ table

I'm trying to update ~5K rows in bq using python client.

Here is my current try:

update_bq(table_id=_ADS_TO_REMOVE_TABLE_NAME, set_clasue="status ='removed'",
where_in_clause=f'''[{[item['ad_id'] for item in
current_ads_removed_json_chunk]}]''')


and

def update_bq(self, table_id, ad_ids, set_clasue, where_in_clause):
table_full_name = self.get_table_full_name(table_id)
query_text = f"""
UPDATE `{table_full_name}`
SET {set_clasue}
WHERE account_id IN {where_in_clause}
"""
query_job = self.client.query(query_text)


How can I map the account id list to a string as follows which seems more efficient(?)

UPDATE mytable SET somefield=( CASE WHEN (id=100) THEN 'some value removed' WHEN (id=101) THEN 'some value removed' END ) WHERE id IN (100,101);

I've tried:

f'''UPDATE mytable SET somefield=( CASE WHEN id={['(id=100) THEN some value \'removed\''.join( item['ad_id'] for item in current_ads_removed_json_chunk]}]+"WHERE id IN ("+ item['ad_id'] for item in current_ads_removed_json_chunk]);


Plus will the bq auto timestamp work on update (works for me on insertion).

Is it better to just remove all the rows with these ids and re-insert with the new status?

Continue reading...