223 Views
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 thetarget_table
by setting its columns to the corresponding values from the CTE. - The
FROM
clause joins the CTE with thetarget_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 theemployee_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 theemployees
table with the new salaries from the CTE, joining on theemployee_id
column.
Make sure to adjust the table names, column names, and conditions according to your specific use case.