Recently, I received an email from an old college friend who had a question about updating data in one table based on conditions from another table. In this blog post, I will explain how to achieve this using the UPDATE FROM SELECT statement in SQL Server.

Let’s consider a scenario where we have two tables: ItemList and ItemPrice. The requirement is to update the Price column in the ItemPrice table with the US price, which is obtained by dividing the existing price by 60.

First, let’s create the sample tables and insert some data:

USE tempdb;
GO

CREATE TABLE ItemList
(ID INT, ItemDesc VARCHAR(100), Country VARCHAR(100));

INSERT INTO ItemList (ID, ItemDesc, Country)
SELECT 1, 'Car', 'USA'
UNION ALL
SELECT 2, 'Phone', 'India'
UNION ALL
SELECT 3, 'Computer', 'USA';

CREATE TABLE ItemPrice
(ID INT, Price VARCHAR(100));

INSERT INTO ItemPrice (ID, Price)
SELECT 1, 5000
UNION ALL
SELECT 2, 10000
UNION ALL
SELECT 3, 20000;
GO

Now, let’s write the script to update the Price column in the ItemPrice table based on the conditions:

-- Update Statement
UPDATE ItemPrice
SET Price = Price/60
FROM ItemList il
INNER JOIN ItemPrice ip ON il.ID = ip.ID
WHERE Country = 'USA';
GO

After executing the above script, the Price column in the ItemPrice table will be updated according to the conditions specified. You can verify the results by selecting the data from the ItemPrice table:

-- Select Data
SELECT *
FROM ItemPrice;

Finally, if you want to clean up the tables, you can drop them using the following script:

-- Clean up
DROP TABLE ItemPrice;
DROP TABLE ItemList;
GO

I hope this quick script helps you understand how to update data in one table based on conditions from another table in SQL Server. If you have any further questions or if there is a better alternative, please let me know.