How to Update Rows in Snowflake

Snowflake reports how many rows were updated but has no RETURNING clause, so run the WHERE as a SELECT first if you want to see what you are about to change. Micro-partitions are immutable, so an update rewrites whole partitions rather than individual rows, and a small update spread across many partitions can be surprisingly expensive.

UPDATE orders
SET status = 'shipped'
WHERE id = 42;

Update from another table

The join condition goes in WHERE. If one order matches several customers, the outcome depends on ERROR_ON_NONDETERMINISTIC_UPDATE, which is off by default, so Snowflake silently picks one. Setting it to TRUE at session level turns that into an error instead, which has saved more than one quietly wrong backfill.

UPDATE orders o
SET o.region = c.region
FROM customers c
WHERE o.customer_id = c.id;