setting multiple column using one update
Master System Design with Codemia
Enhance your system design skills with over 120 practice problems, detailed solutions, and hands-on exercises.
Introduction
Updating multiple columns in one UPDATE statement is standard SQL and usually the right way to make related changes to the same row set. It is clearer, more atomic, and often more efficient than issuing several separate updates for the same records.
Basic Syntax
The core pattern is:
Everything after SET is a comma-separated list of column assignments. All assignments happen as part of one statement.
Why One Update Is Better Than Several
If you split related changes across multiple statements, you create extra work and more opportunity for inconsistency.
Bad pattern:
Better pattern:
The single statement is easier to review and keeps the row transition together.
Use Expressions in the Same Statement
You are not limited to fixed literal values. Each column can be updated with an expression.
This is a strong pattern because business logic stays consistent within one row update.
Update Multiple Columns from Another Table
Many SQL engines also let you update columns from a joined source. Syntax varies by database, but the idea is common.
PostgreSQL example:
This is useful for backfills, denormalized summary tables, and repair scripts.
Conditional Multi-Column Updates
You can vary each assignment with CASE so one statement handles several scenarios cleanly.
This avoids writing one update for status and another update for reason.
Transaction Safety
A single UPDATE statement is usually atomic by itself, but you still need transaction awareness when it is part of a larger workflow.
The point is not that multiple columns require a transaction. The point is that related multi-row operations usually do.
Performance Notes
One multi-column update is usually better than several separate updates on the same rows because:
- fewer round trips
- less repeated locking work
- one clearer execution plan
- one logical change instead of several partial changes
That does not mean every large update is cheap. A wide update against millions of rows can still be expensive, especially if many indexes must be maintained.
Common Pitfalls
The biggest mistake is forgetting the WHERE clause. A multi-column update without a filter can change every row in the table.
Another issue is assuming assignment order matters. In many databases, expressions are evaluated based on the original row values rather than sequentially like imperative code. Check your database rules before relying on one assignment to feed another.
A third problem is spreading related column updates across multiple statements for no reason. That makes auditing and rollback harder.
Summary
- Use one
UPDATEstatement with a comma-separatedSETlist to change multiple columns. - A single statement is usually clearer and more atomic than several smaller updates.
- You can use expressions,
CASE, and joined data sources in the same update. - Be careful with
WHERE, especially in production data fixes. - Treat large or multi-row workflows as transaction design problems, not just syntax problems.

