How do I UPDATE from a SELECT in SQL Server?

Question

Aryan Kumar · Answer

To update from a select in SQL Server, you can use the UPDATE statement with the FROM clause. The UPDATE statement allows you to update rows in a table, and the FROM clause allows you to select rows from another table.

The syntax for updating from a select in SQL Server is as follows:

SQL

UPDATE table_name
SET column1 = value1,
    column2 = value2,
    ...
FROM table1
WHERE condition;

The table_name is the name of the table that you want to update. The column1, column2, etc. are the columns that you want to update. The value1, value2, etc. are the new values for the columns. The table1 is the name of the table that you want to select from. The condition is an optional clause that specifies the rows that you want to update.

For example, the following code will update the CustomerName column in the Customers table to John Smith for all customers where the CustomerID is 100:

SQL

UPDATE Customers
SET CustomerName = 'John Smith'
FROM Customers
WHERE Customers.CustomerID = 100;

This code will first select the row from the Customers table where the CustomerID is 100. Then, it will update the CustomerName column in the Customers table to John Smith for the selected row.

You can also use the FROM clause in the UPDATE statement to select rows from another table and update the rows in the current table based on the selected rows. For example, the following code will update the CustomerName column in the Customers table to the value of the CustomerName column in the Orders table for all customers who have placed an order:

SQL

UPDATE Customers
SET CustomerName = Orders.CustomerName
FROM Customers
JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

This code will first join the Customers and Orders tables on the CustomerID column. Then, it will update the CustomerName column in the Customers table to the value of the CustomerName column in the Orders table for all rows where the CustomerID is the same in both tables.

forum

How do I UPDATE from a SELECT in SQL Server?

Can you answer this question?

1 Answers