How to Update data from other table in MSSQL? Explain with example.
MSSQL Update Command, Update Command With Join, Update From Command
To update data in one table using data from another table in MSSQL, you can use the UPDATE statement with a JOIN or a subquery. This allows you to update specific columns in the target table with values retrieved from the source table. Let's walk through an example to illustrate this:
Suppose we have two tables: "Products" and "ProductUpdates." The "Products" table contains information about products, and the "ProductUpdates" table contains updated information for some of the products.
Products Table:
ProductID | ProductName | Price |
---|---|---|
1 | Laptop | 800 |
2 | Phone | 500 |
3 | Tablet | 300 |
4 | TV | 1000 |
ProductUpdates Table:
ProductID | NewPrice |
---|---|
2 | 550 |
3 | 350 |
Now, let's say we want to update the "Price" column in the "Products" table with the new prices from the "ProductUpdates" table for those products that have updates.
You can achieve this using the following SQL query:
UPDATE Products
SET Price = ProductUpdates.NewPrice
FROM Products
INNER JOIN ProductUpdates ON Products.ProductID = ProductUpdates.ProductID;
Explanation of the query:
- We use the
UPDATE
statement to modify the "Products" table. - The
SET
clause updates the "Price" column in the "Products" table with the corresponding "NewPrice" from the "ProductUpdates" table. - The
FROM
clause specifies the tables involved in the update operation and uses theINNER JOIN
to link the "Products" and "ProductUpdates" tables based on the common column "ProductID." - The
ON
clause specifies the join condition, ensuring that only matching records between the two tables are updated.
After executing the query, the "Products" table will be updated as follows:
Products Table (After Update):
ProductID | ProductName | Price |
---|---|---|
1 | Laptop | 800 |
2 | Phone | 550 |
3 | Tablet | 350 |
4 | TV | 1000 |
As you can see, the "Price" of products with IDs 2 and 3 has been updated with the new prices from the "ProductUpdates" table.
Keep in mind that this approach updates only the matching records in the "Products" table based on the common "ProductID" between the two tables. Products without corresponding updates in the "ProductUpdates" table will remain unchanged.
It's essential to be cautious while updating data and to ensure that you have proper backups before making significant changes to your database.
What's Your Reaction?