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.
UPDATE table SET column = expression WHERE conditionchanges the rows the condition picks. EveryUPDATEneeds aWHERE. To change every row, writeWHERE TRUE.- A
SETexpression can read the old value of the row, as inSET price = price + 1. Prices areNUMERIC. A decimal literal such as1.1takes that type when you multiply, soROUND(price * 1.1, 2)is aNUMERICwith two decimals. AFLOAT64value cannot be stored in aNUMERICcolumn. - A row is changed only when the condition is true. A row where the condition is false or
NULLstays as it is. UPDATE ... FROMpairs each target row with the rows of another table that match theWHERE, and theSETcan read the columns of that table. A target row with no partner is not paired, so it stays as it is.
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.