How do you replace every occurrence of a specific status value in SQL?

Instruction: Write SQL to replace every occurrence of 'oldValue' with 'newValue' in orders.status. Preserve other values and NULLs, and explain the effect of omitting WHERE.

Context: This question tests a scoped UPDATE that replaces matching values while preserving other rows.

Updated

Official answer

To replace occurrences of one status while preserving other statuses, include the matching predicate:

UPDATE orders
SET status = 'newValue'
WHERE status = 'oldValue';

This changes every matching row, not every row in the table. Other status values and NULLs remain unchanged. Omitting WHERE would replace them too, which is a different operation.

Bind application values, confirm the intended scope and check affected rows. A preview SELECT is useful but does not prevent concurrent changes; use the transaction and concurrency strategy appropriate to the operation. PostgreSQL can add RETURNING to inspect affected IDs. Plan bounded execution and lock impact for a large update, while preserving any required all-or-nothing guarantee.

Related Questions