UPDATE safely

A wrong UPDATE changes data that other reports depend on, and it gives no warning. You choose the rows with WHERE and the new values with SET.

How it works

UPDATE looks at each row of the table. The SET assignments rewrite the rows for which the WHERE condition is true, and every other row stays as it was.

Common mistake

A condition that is true for more rows than you meant changes all of them. Run the same condition in a SELECT first and look at the rows it picks.

Worked example

CREATE TEMP TABLE sale_prices AS
SELECT product_id, price FROM `learn.shop.products` WHERE product_id IN (1, 2);
UPDATE sale_prices SET price = ROUND(price * NUMERIC '0.9', 2) WHERE product_id = 1;
SELECT product_id, price FROM sale_prices ORDER BY product_id;

The temporary copy starts with the prices 24.99 and 89.99. The SET expression reads the old price and gives product 1 a discount of ten percent: 22.49.

Product 2 stays at 89.99, because its id does not match the WHERE. The last query shows the changed row and the unchanged one.

All BigQuery lessons