An email from an old college friend landed my mailbox about UPDATE FROM SELECT Statement with Condition:
I have two tables. I want to conditionally update data in one table based on another table. How can I do that. I have included sample scripts and an image for further explanation.
It always delights to receive email from an old college friend and particularly it is even more interesting when they have a question w where I can help. Here is the question and a sample script.
User had two tables – ItemList and ItemPrice. The requirement was to update ItemPrice table column Price with US price and for that it required to divide the column by 60. Here is the sample script of the table displayed in the image.
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'; GO 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 -- SELECT Data SELECT * FROM ItemList; SELECT * FROM ItemPrice;
Now let us write a script which will update the table as per our expectation.
-- Update Statement UPDATE ItemPrice SET Price = Price/60 FROM ItemList il INNER JOIN ItemPrice ip ON il.ID = ip.ID WHERE Country = 'USA' GO
Now let us result by selecting the data in our Price table.
Now you can see how we can update from table to another table with conditions. You can clean up above code by dropping tables.
-- Clean up DROP TABLE ItemPrice; DROP TABLE ItemList; GO
I hope this quick script helps, let me know if there is any better alternative.
Reference: Pinal Dave (https://blog.sqlauthority.com)