Skip to content
Advertisement

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:

and

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:

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?

Advertisement

Answer

I don’t know if this will be more effciet, but you can try to create whole sql script in python. Script can contains all values. Values will be passed as inject_table, and then you can update all rows with a signle query.

Doc: inject table and UPDATE FROM

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement