# UPDATE or DELETE statement over table X would affect rows in the streaming buffer > The table has rows that were streamed in recently, and BigQuery will not run an UPDATE, a DELETE or a MERGE that updates or deletes while they are in the streaming buffer. Rows written through the legacy insertAll method or the REST Storage Write API stay in the buffer for about 30 minutes. Wait, insert instead, or write through gRPC next time. Source: https://docs.querylab.io/bigquery-errors/update-or-delete-statement-would-affect-rows-in-the-streaming-buffer/ Updated: 2026-11-13 ```text UPDATE or DELETE statement over table my-project.my_dataset.events would affect rows in the streaming buffer, which is not supported ``` The text above is BigQuery's message with a table name filled in. The table name is the full `project.dataset.table`. ## What BigQuery means Rows that are written to a table by streaming first go to a streaming buffer. The table has rows in the buffer, and your statement would change them. BigQuery refuses the whole statement. Only some streaming methods cause this. Google documents that rows written through the legacy `tabledata.insertAll` method, or through the REST interface of the Storage Write API, cannot be changed by DML for about 30 minutes after the write. Rows written through the gRPC interface of the Storage Write API can be changed right away. It is a runtime error. The statement is valid and a dry run passes. It only fails when the table has rows in the buffer at the moment it runs, so the same statement can work an hour later. ## Which statements fail These fail on a table that has rows in the streaming buffer: - `UPDATE` - `DELETE` - `MERGE` with `WHEN MATCHED THEN UPDATE` or `WHEN MATCHED THEN DELETE` These ran on the same kind of table: - `INSERT` - a `MERGE` that only inserts - `DELETE ... WHERE FALSE` Queries read the buffered rows like any other rows. A dry run counts 0 bytes for them, so the estimate of a query can be lower than the bytes the run reads. ## The fix **Wait.** Google puts the limit at about 30 minutes after the write. Run the statement again later. If the error comes back, the table is still receiving rows through the same method. **Insert instead of changing.** `INSERT` and a `MERGE` that only inserts work while rows are in the buffer. If the change can be a new row, for example a corrected version of a row with a later timestamp, add the row and let the query pick the newest one per key. See [MERGE](https://docs.querylab.io/bigquery/merge/) and [QUALIFY](https://docs.querylab.io/bigquery/qualify/). **Stream through gRPC next time.** The gRPC Storage Write API has no such limit. Move the writer to it when you need to change rows soon after they arrive. See [Streaming data into BigQuery](https://docs.querylab.io/bigquery/streaming/). ## How to see the buffer The metadata of a table (`tables.get`) has a `streamingBuffer` field while the table has buffered rows: ```text streamingBuffer: { estimatedBytes, estimatedRows, oldestEntryTime } ``` `oldestEntryTime` is the time of the oldest row in the buffer. In our check, a table that held only two streamed rows had `estimatedRows` of 2 and a `numRows` of `"0"`. `numRows` leaves the buffered rows out, so it is not the count of rows you get from `SELECT COUNT(*)`. `tabledata.list` returns the buffered rows, but its `totalRows` can lag behind. It showed `"0"` while rows came back. ## Different errors that look like this one **`UPDATE/MERGE must match at most one source row for each target row`.** A different problem: the source has duplicate keys. See [UPDATE/MERGE must match at most one source row](https://docs.querylab.io/bigquery-errors/update-merge-must-match-at-most-one-source-row/). ## In Querylab.io The editor does not mark this error. The schema the editor uses has no streaming buffer in it, and a dry run passes, so the error comes back from BigQuery when the statement runs. The table's Overview tab shows a Streaming buffer row, with the estimated rows and size, while the table has one. Check it before you run an `UPDATE` or `DELETE` on a table that is fed by streaming. After a failed run, the error dialog has an About this error link to this page. There is no fix button for it, because the way out is to wait or to change how the rows are written. ## Sources Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery: - [DML statements, limitations](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-manipulation-language) - [Storage Write API, DML with recently streamed data](https://cloud.google.com/bigquery/docs/write-api)