SQL Server: Update Data from SELECT Query – Complete Guide

In SQL Server, you can use a common table expression (CTE) with the UPDATE statement to update a table based on the result of a SELECT query. Here’s a basic syntax for performing an update from a select in SQL Server:

WITH CTE AS (
    SELECT column1, column2, ... -- Columns you want to select for updating
    FROM your_table_name
    WHERE your_condition -- Optional: Add conditions to filter the rows you want to update
)
UPDATE target_table
SET target_column1 = CTE.column1, -- Update target columns with values from the CTE
    target_column2 = CTE.column2,
    ...
FROM CTE
JOIN target_table ON join_condition; -- Join the CTE with the target table on a specific condition

Explanation:

• The common table expression (CTE) is defined using the WITH clause, which selects the columns you want to update from the source table (your_table_name).
• You can include a WHERE clause in the CTE to filter the rows you want to update based on specific conditions.
• The UPDATE statement updates the target_table by setting its columns to the corresponding values from the CTE.
• The FROM clause joins the CTE with the target_table using a specific join condition.

Example:
Let’s say you have a table named employees and you want to update the salary column based on a select query that calculates a new salary. Here’s how you can do it:

WITH NewSalaries AS (
    SELECT employee_id, 
           salary * 1.1 AS new_salary -- Increase the salary by 10%
    FROM employees
    WHERE department_id = 1 -- Only update employees in department 1
)
UPDATE employees
SET salary = NewSalaries.new_salary
FROM NewSalaries
WHERE employees.employee_id = NewSalaries.employee_id;

In this example:

• We define a CTE named NewSalaries that selects the employee_id and calculates a new salary by increasing the existing salary by 10% for employees in department 1.
• We then use the UPDATE statement to update the employees table with the new salaries from the CTE, joining on the employee_id column.

Make sure to adjust the table names, column names, and conditions according to your specific use case.