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

MSSQL Update Command, Update Command With Join, Update From Command

Jul 30, 2023 - 13:43
Jul 30, 2023 - 13:57
 0
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:

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:

  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):

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?

like

dislike

love

funny

angry

sad

wow

Dr. Deepak Kumar Experienced Managing Director with a demonstrated history of working in the information technology and services industry. Skilled in Management · Team Building · Cloud Migration · Market Research · Team Leadership · Research Projects · New Business Development · Time Management · Analytical Skills. Strong business development professional with a Doctorate focused in Computer Programming from BIT Mesra.