How To Update Multiple Columns In Sql

7 min read

How to Update Multiple Columns in SQL

If you're working with relational databases, you may often need to modify several fields at once. Even so, this article explains how to update multiple columns in SQL, covering various methods, best practices, and common pitfalls. Whether you’re using MySQL, PostgreSQL, SQL Server, Oracle, or SQLite, the techniques described here will help you write efficient, error‑free statements that keep your data consistent.

Introduction

The UPDATE statement is one of the core Data Manipulation Language (DML) commands in SQL. While updating a single column is straightforward, real‑world applications frequently require changing multiple columns simultaneously—such as when a user profile is edited, when a product’s price and stock levels are adjusted, or when a transaction log needs to reflect several status fields. Mastering how to update multiple columns in SQL not only saves time but also ensures data integrity by allowing you to perform atomic updates within a single transaction That's the part that actually makes a difference..

Steps to Update Multiple Columns

1. Use a Single UPDATE Statement with Multiple Column Assignments

The most direct approach is to list each column and its new value within the same UPDATE clause. This keeps the operation atomic and reduces the number of round‑trips to the database.

UPDATE employees
SET    first_name = 'Jane',
       last_name  = 'Doe',
       salary     = 75000,
       department = 'Engineering'
WHERE  employee_id = 101;
  • SET clause: Separate each column assignment with a comma.
  • WHERE clause: Limit the change to the intended rows; omitting it will update every row in the table.

2. Update from Another Table (JOIN Update)

When you need to copy values from one table to another, a JOIN can be used within the UPDATE statement. This pattern is common for synchronizing data or applying calculated values Less friction, more output..

UPDATE t1
SET    t1.status   = t2.new_status,
       t1.priority = t2.priority
FROM   source_table AS t2
WHERE  t1.id = t2.id
  AND  t2.update_flag = 1;
  • FROM clause: Specifies the source of new values.
  • JOIN type: Use INNER JOIN for matching rows, LEFT JOIN if you want to preserve rows that lack a counterpart.

3. Use a Subquery for Complex Calculations

For more sophisticated updates—such as setting a column based on an aggregate or a derived value—subqueries are invaluable Simple, but easy to overlook. That's the whole idea..

UPDATE products
SET    price = (
         SELECT AVG(list_price)
         FROM   product_histories
         WHERE  product_id = products.id
       ),
       last_updated = CURRENT_TIMESTAMP
WHERE  price <> (
         SELECT AVG(list_price)
         FROM   product_histories
         WHERE  product_id = products.id
       );
  • The subquery computes the new price per product.
  • The outer UPDATE applies the change only where the current price differs from the computed average, avoiding unnecessary writes.

4. take advantage of Common Table Expressions (CTEs)

CTEs make multi‑column updates more readable, especially when the logic involves multiple derived columns.

WITH calc AS (
  SELECT
    id,
    unit_price * quantity AS extended_price,
    CASE
        WHEN quantity > 10 THEN 0.10
        ELSE 0.05
    END AS discount_rate
  FROM orders
)
UPDATE orders
SET    extended_price = calc.extended_price,
       discount_rate  = calc.discount_rate
FROM   calc
WHERE  orders.id = calc.id;
  • The CTE calc prepares the new values.
  • The main UPDATE joins back to the CTE on the key column.

5. Batch Updates with Window Functions

When you need to reference ranking or cumulative calculations, window functions can be embedded directly in the UPDATE Easy to understand, harder to ignore..

UPDATE employees
SET    bonus = salary * 0.1,
       rank  = RANK() OVER (ORDER BY salary DESC)
WHERE  department = 'Sales';
  • RANK() assigns a position based on salary.
  • The SET clause updates both bonus and rank in one pass.

Scientific Explanation

Atomicity and Transactions

Updating multiple columns in a single UPDATE statement guarantees atomicity: either all column changes are applied, or none are. This property is crucial for maintaining referential integrity and preventing partial updates that could leave the database in an inconsistent state. Wrapping the statement in a transaction (e.Here's the thing — g. , BEGIN TRANSACTION; … COMMIT;) further protects against concurrent modifications Less friction, more output..

Performance Considerations

  • Index Impact: Updating indexed columns can be costly because the index must be rebuilt. If you frequently modify multiple indexed fields, consider whether denormalizing or using covering indexes could reduce overhead.
  • Locking Behavior: Different database engines lock rows or pages differently. In MySQL with InnoDB, an UPDATE acquires a row‑level lock, while SQL Server may lock the entire page. Understanding the locking granularity helps you design updates that minimize contention.
  • Optimizer Choices: Modern RDBMS optimizers can rewrite multi‑column updates for better execution plans. On the flip side, complex subqueries or CTEs may force a full table scan. Use EXPLAIN (or equivalent) to verify that the plan is efficient.

Best Practices

  1. Test on a Copy: Always run the update on a non‑production replica first. Verify the WHERE clause selects the intended rows.
  2. Use Transactions: Begin a transaction, execute the update, and commit only after confirming the changes are correct.
  3. Backup Critical Data: Before bulk updates, ensure you have a recent backup or point‑in‑time recovery capability.
  4. Limit Scope: If possible, narrow the update to a subset of rows using a selective WHERE condition. This reduces the amount of data written and the associated I/O.
  5. Avoid Updating NULL with DEFAULT: When setting a column to NULL or a default value, be explicit about the intention to avoid unintended side effects.

Frequently Asked Questions

Q: Can I update multiple columns from a single source column?
A: Yes. You can assign the same source column to multiple target columns in the SET clause, or use a CASE expression to distribute its value That's the part that actually makes a difference..

UPDATE tbl
SET    col1 = src.value,
       col2 = src.value * 1.1
FROM   source AS src
WHERE  tbl.id = src.id;

Q: What happens if I forget the WHERE clause?
A: The update will affect every row in the table. This can lead to massive data loss. Always include a restrictive WHERE condition unless you intentionally intend a wholesale change.

Q: Is it possible to update based on a self‑join?
A

A: Yes—provided you alias the target table and join it to itself on a stable key. The syntax varies by dialect, but the pattern is the same: identify the row to update, join the same table to supply the new values, and constrain the join so each target row matches at most one source row.

-- PostgreSQL / SQL Server-style pattern
UPDATE target
SET    target.col1 = source.col1,
       target.col2 = source.col2
FROM   tbl AS target
JOIN   tbl AS source
  ON   target.id = source.parent_id
WHERE  source.status = 'approved';

In MySQL, the equivalent often looks like:

UPDATE tbl AS target
JOIN   tbl AS source
  ON   target.id = source.parent_id
SET    target.col1 = source.col1,
       target.col2 = source.col2
WHERE  source.status = 'approved';

Use a self-join only when the relationship is unambiguous. If the join can produce multiple source rows for one target row, the final values may be nondeterministic. In that case, aggregate the source rows first or use a correlated subquery with a deterministic ordering Small thing, real impact. Simple as that..

Q: How can I preview the rows an UPDATE will affect?
A: Replace the UPDATE … SET portion with a SELECT that uses the same FROM, JOIN, and WHERE clauses. Review the result set, confirm the row count, and only then run the update inside a transaction.

Q: Should I use a CTE for complex updates?
A: Yes, when the logic is easier to express as a staged query. A CTE can isolate the rows to be changed, compute derived values, or deduplicate a join before the update. Keep in mind that some engines materialize CTEs and others inline them, so check the execution plan for large data sets Worth knowing..

Q: What is the safest way to perform a bulk update?
A: Batch the work. Update in chunks using a key range or a limited subquery, commit each batch, and monitor replication lag and lock waits. This keeps transactions short and makes recovery easier if a batch fails.

Conclusion

Updating multiple columns is conceptually simple, but production safety depends on precise row selection, transactional discipline, and awareness of indexing and locking costs. On the flip side, use explicit WHERE clauses, preview with SELECT, test on a replica, and keep bulk changes batched and reversible. And when the update depends on related rows, self-joins and CTEs can express the logic cleanly—provided the join is deterministic. Treat every multi-column update as a controlled data change: verify the scope, protect the data, and confirm the result before committing Most people skip this — try not to..

Latest Batch

Just Dropped

Others Went Here Next

Along the Same Lines

Thank you for reading about How To Update Multiple Columns In Sql. We hope the information has been useful. Feel free to contact us if you have any questions. See you next time — don't forget to bookmark!
⌂ Back to Home