Logo
Back

How do I UPDATE from a SELECT in SQL Server?

When it comes to updating records in SQL Server based on data in another table (or even within the same table), the “UPDATE from SELECT” technique is incredibly useful. This scenario often arises when you need to synchronize data across tables, apply bulk changes, or correct mismatched information.

Below, we’ll explore various approaches to UPDATE from SELECT in SQL Server, discuss best practices, and highlight additional resources for mastering SQL and database concepts.

Understanding the Basic “UPDATE from SELECT” Pattern

The most common approach to UPDATE rows in SQL Server using another table’s data is via a JOIN. Here’s the general pattern:

UPDATE targetTable
SET targetTable.colToUpdate = sourceTable.colWithNewValue
FROM targetTable
JOIN sourceTable
    ON targetTable.joiningColumn = sourceTable.joiningColumn
WHERE <optional filtering conditions>;
  1. targetTable: The table you want to update.
  2. sourceTable: The table (or subquery) that contains the new values.
  3. joiningColumn: The column (or columns) that establish the relationship between targetTable and sourceTable.
  4. colToUpdate: The column in targetTable that will be updated.
  5. colWithNewValue: The column in sourceTable holding the data you want to use for the update.
  6. WHERE clause (optional): Filters the rows that need to be updated to avoid unintended changes.

Example: Updating Customer Email from a Temporary Data Source

Let’s say you have a temporary table TempEmails that holds updated customer emails and you want to reflect these changes in your Customers table:

-- Suppose TempEmails has the columns: customer_id, new_email
UPDATE Customers
SET Customers.email = TempEmails.new_email
FROM Customers
JOIN TempEmails
  ON Customers.customer_id = TempEmails.customer_id
WHERE Customers.email <> TempEmails.new_email;
  • JOIN ensures you only update rows where there’s a matching customer_id.
  • WHERE avoids unnecessary updates by only updating rows where the email has actually changed.

Using a CTE (Common Table Expression)

Another powerful method is using a Common Table Expression (CTE) for more complex scenarios or to improve readability:

WITH UpdatedEmails AS (
    SELECT
        C.customer_id,
        T.new_email
    FROM Customers C
    JOIN TempEmails T
      ON C.customer_id = T.customer_id
    -- Additional filters or transformations can go here
)
UPDATE UpdatedEmails
SET new_email = 'some_transformed_value'; -- Example transformation
  • In this approach, the CTE UpdatedEmails first selects all the data you want to change.
  • Then the UPDATE statement directly updates the CTE, which in turn updates the underlying table.
  • This method is especially handy for multi-step transformations where you chain multiple CTEs for clarity.

Best Practices for Updating via SELECT

  1. Always test with a SELECT first: Before running the actual UPDATE, execute a SELECT with the same JOIN logic to see which rows will be affected.
  2. Perform a backup (for production databases): Particularly for large or critical tables. Mistakes can be costly.
  3. Use transactions: Wrap your UPDATE in a transaction so you can roll back if the result isn’t what you expected.
  4. Apply targeted WHERE clauses: Avoid updating rows blindly. Filter down to the records that must be changed.
  5. Watch out for triggers: If your table has triggers, they will fire during bulk updates. Make sure that’s the desired behavior.

Handling More Complex Cases

  • Multiple columns: You can update several columns simultaneously as long as you map them correctly to the source.

  • Derived or computed values: Instead of directly assigning columns from the source table, you can use an expression. For example:

    UPDATE A
SET A.col1 = B.col2 + '_updated',
    A.col2 = B.col3 * 100
FROM TableA A
JOIN TableB B ON A.id = B.id
WHERE ...

  • Different schemas or databases: If your source table is in a different schema or database, prefix the table name with [DatabaseName].[SchemaName].TableName.

Level Up Your SQL Skills

Updating data is just one essential skill in your SQL toolbox. If you’re preparing for interviews or want to enhance your database expertise, consider these comprehensive courses from DesignGurus.io:

  1. Grokking SQL for Tech Interviews

    • Master common SQL patterns, advanced queries, and crucial interview topics.

  2. Grokking Database Fundamentals for Tech Interviews

    • Learn about indexing, normalization, and crucial database concepts to ensure high performance and scalability.

  3. Relational Database Design and Modeling for Software Engineers

    • Take a deep dive into modeling real-world data efficiently, advanced SQL techniques, and best design practices.

Explore Further for a Complete Interview Prep

  • Mock Interviews: Get personalized feedback from ex-FAANG engineers with Coding Mock Interview and System Design Mock Interview.
  • Interview BootCamp: For a structured approach, check out the Interview BootCamp to prepare in a guided, timeline-focused manner.
  • DesignGurus YouTube Channel: Visit here for free tutorials and deep dives, including SQL best practices, system design, and coding interview tips.

Final Thoughts

Updating data via a SELECT in SQL Server is a powerful capability that can streamline your workflow and reduce manual overhead. By using JOINs, CTEs, and careful filtering, you can ensure that only the necessary rows are updated. Whether you’re synchronizing tables, applying large-scale transformations, or correcting mismatched data, mastering this pattern is essential for any database professional or software engineer.

For a deeper dive into SQL, database fundamentals, and interview preparation, explore the courses on DesignGurus.io. Armed with these skills, you’ll be well-prepared to handle advanced SQL tasks in both interviews and production environments. Good luck on your journey toward becoming an SQL pro!

CONTRIBUTOR
TechGrind