How to Update data from other table in MSSQL? Explain with example.

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:

ProductUpdates Table:

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:

1. We use the UPDATE statement to modify the "Products" table.
2. The SET clause updates the "Price" column in the "Products" table with the corresponding "NewPrice" from the "ProductUpdates" table.
3. The FROM clause specifies the tables involved in the update operation and uses the INNER JOIN to link the "Products" and "ProductUpdates" tables based on the common column "ProductID."
4. 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):

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.