{"id":8393,"date":"2024-04-26T08:49:45","date_gmt":"2024-04-26T08:49:45","guid":{"rendered":"https:\/\/www.infinitivehost.com\/knowledge-base\/?p=8393"},"modified":"2024-04-26T08:51:17","modified_gmt":"2024-04-26T08:51:17","slug":"sql-server-update-data-from-select-query-complete-guide","status":"publish","type":"post","link":"https:\/\/www.infinitivehost.com\/knowledge-base\/sql-server-update-data-from-select-query-complete-guide\/","title":{"rendered":"SQL Server: Update Data from SELECT Query – Complete Guide"},"content":{"rendered":"
<\/span> 1,665<\/span> Views<\/span><\/div>\n

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

WITH CTE AS (\n    SELECT column1, column2, ... -- Columns you want to select for updating\n    FROM your_table_name\n    WHERE your_condition -- Optional: Add conditions to filter the rows you want to update\n)\nUPDATE target_table\nSET target_column1 = CTE.column1, -- Update target columns with values from the CTE\n    target_column2 = CTE.column2,\n    ...\nFROM CTE\nJOIN target_table ON join_condition; -- Join the CTE with the target table on a specific condition<\/code><\/code><\/pre>\n\n\n\n
Explanation:<\/p>\n\n\n\n
    \n
  • The common table expression (CTE) is defined using the WITH<\/code> clause, which selects the columns you want to update from the source table (your_table_name<\/code>).<\/li>\n\n\n\n
  • You can include a WHERE<\/code> clause in the CTE to filter the rows you want to update based on specific conditions.<\/li>\n\n\n\n
  • The UPDATE<\/code> statement updates the target_table<\/code> by setting its columns to the corresponding values from the CTE.<\/li>\n\n\n\n
  • The FROM<\/code> clause joins the CTE with the target_table<\/code> using a specific join condition.<\/li>\n<\/ul>\n\n\n\n
    Example:
    Let’s say you have a table named employees<\/code> and you want to update the salary<\/code> column based on a select query that calculates a new salary. Here’s how you can do it:<\/p>\n\n\n\n
    WITH NewSalaries AS (\n    SELECT employee_id, \n           salary * 1.1 AS new_salary -- Increase the salary by 10%\n    FROM employees\n    WHERE department_id = 1 -- Only update employees in department 1\n)\nUPDATE employees\nSET salary = NewSalaries.new_salary\nFROM NewSalaries\nWHERE employees.employee_id = NewSalaries.employee_id;<\/code><\/code><\/pre>\n\n\n\n
    In this example:<\/p>\n\n\n\n
      \n
    • We define a CTE named NewSalaries<\/code> that selects the employee_id<\/code> and calculates a new salary by increasing the existing salary by 10% for employees in department 1.<\/li>\n\n\n\n
    • We then use the UPDATE<\/code> statement to update the employees<\/code> table with the new salaries from the CTE, joining on the employee_id<\/code> column.<\/li>\n<\/ul>\n\n\n\n
      Make sure to adjust the table names, column names, and conditions according to your specific use case.<\/p>\n","protected":false},"excerpt":{"rendered":"
      1,665 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: Explanation: Example:Let’s say you have a table named employees and you want […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[62],"tags":[],"class_list":["post-8393","post","type-post","status-publish","format-standard","hentry","category-web-hosting-manager"],"_links":{"self":[{"href":"https:\/\/www.infinitivehost.com\/knowledge-base\/wp-json\/wp\/v2\/posts\/8393","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.infinitivehost.com\/knowledge-base\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.infinitivehost.com\/knowledge-base\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.infinitivehost.com\/knowledge-base\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.infinitivehost.com\/knowledge-base\/wp-json\/wp\/v2\/comments?post=8393"}],"version-history":[{"count":1,"href":"https:\/\/www.infinitivehost.com\/knowledge-base\/wp-json\/wp\/v2\/posts\/8393\/revisions"}],"predecessor-version":[{"id":8394,"href":"https:\/\/www.infinitivehost.com\/knowledge-base\/wp-json\/wp\/v2\/posts\/8393\/revisions\/8394"}],"wp:attachment":[{"href":"https:\/\/www.infinitivehost.com\/knowledge-base\/wp-json\/wp\/v2\/media?parent=8393"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.infinitivehost.com\/knowledge-base\/wp-json\/wp\/v2\/categories?post=8393"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.infinitivehost.com\/knowledge-base\/wp-json\/wp\/v2\/tags?post=8393"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}