BigQuery DML限额问题：用INSERT...SELECT替代UPDATE失败求助

Got it, let's break down how to solve this BigQuery DML quota problem you're hitting. First, let's clarify why your INSERT ... SELECT approach isn't working—because that method adds records to a new table (TABLE2) but doesn't modify the original TABLE1 data you intended to update. Let's jump into actionable solutions:

1. Use MERGE for Batch Updates/Deletes #

BigQuery counts a single MERGE statement as one DML operation, regardless of how many rows it modifies. This is perfect for bypassing the 96 daily UPDATE/DELETE limit, since you can handle all your updates in one go.

Example syntax for updating TABLE1:

MERGE INTO TABLE1 t1
USING (
  -- Your query to get the records that need updates
  SELECT id, new_col1, new_col2 FROM your_source_data WHERE ...
) t2
ON t1.id = t2.id -- Match on your unique key
WHEN MATCHED THEN
  UPDATE SET
    t1.col1 = t2.new_col1,
    t1.col2 = t2.new_col2;

You can even combine updates and deletes in the same MERGE if needed:

MERGE INTO TABLE1 t1
USING (
  SELECT id, action_type, new_col FROM ...
) t2
ON t1.id = t2.id
WHEN MATCHED AND t2.action_type = 'update' THEN
  UPDATE SET col = t2.new_col
WHEN MATCHED AND t2.action_type = 'delete' THEN
  DELETE;

2. Batch Multiple Updates into a Single UPDATE Statement #

Instead of sending separate UPDATE statements for each row or small group, use CASE WHEN clauses to handle multiple update conditions in one query. This counts as a single DML operation, saving your quota.

Example:

UPDATE TABLE1
SET
  col1 = CASE
    WHEN status = 'active' THEN 'processed'
    WHEN status = 'pending' THEN 'review'
    ELSE col1 -- Keep original value if no match
  END,
  col2 = CASE
    WHEN priority = 'high' THEN CURRENT_TIMESTAMP()
    ELSE col2
  END
WHERE status IN ('active', 'pending') OR priority = 'high';

3. Partition Table + Replace Partitions (For Large Datasets) #

If your table is partitioned (e.g., by date), you can isolate the partition needing updates, modify it in a temp table, then merge it back. This is efficient for large tables and avoids hitting row-level update limits.

Steps:

1. Extract the target partition to a temp table:

   CREATE OR REPLACE TABLE temp_partition_data AS
   SELECT * FROM TABLE1 WHERE _PARTITIONDATE = '2024-05-20';
   

2. Update the temp table (this uses one DML operation):

   UPDATE temp_partition_data
   SET col1 = 'updated_value' WHERE ...;
   

3. Merge the updated temp table back into the original partition:

   MERGE INTO TABLE1 t1
   USING temp_partition_data t2
   ON t1.id = t2.id AND t1._PARTITIONDATE = t2._PARTITIONDATE
   WHEN MATCHED THEN UPDATE SET * EXCEPT(id, _PARTITIONDATE)
   WHEN NOT MATCHED THEN INSERT *;
   

Why Your INSERT ... SELECT Didn't Work #

Just to clarify: INSERT ... SELECT adds new records to TABLE2, but it doesn't alter the existing records in TABLE1. If your goal was to update TABLE1's data, this method won't modify the original table—that's why it didn't produce the effect you wanted.

内容的提问来源于stack exchange，提问作者searain