An email from an old college friend landed my mailbox about UPDATE FROM SELECT Statement with Condition:
“Hey Pinal,”
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.
Thanks!”
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)
11 Comments. Leave new
say there are several countries,this way we have to update accordingly.Also it is difficult to analyze if having various currency price in table.I will keep price in one currency only and the at the time of displaying (as per requirement),i convert them,which will be easier.Same currency value will help in several analytic reports.
For me the more readable form would be:
update ItemPrice
set Price = Price / 60
where ID in (select ID from ItemList where Country = ‘USA’);
Do You see any disadvantage of my idea ?
Hi Pinal
I have been following your blog posts for a while and find it very nice to keep on learning bit by bit every day!
I have started my own blog ) and I am trying to learn as I blog along. Sure, there will be some mistakes and problems, but I am prepared to learn. I have also bought your book on how to start up a blog. I am also making progress with that.
I am also involved in the Johannesburg SQL Usergroup and I am preparing to give a talk on SQL Query plans at our next meeting on 14 October.
I have chosen the subject because I knew very little of it and wanted to force myself to learn as I prepare for the meeting.
I have watched half of your resent course on Pluralsight on the topic and should finish it by the weekend.
One question please, is there an easy way to keep the color of the text for the code snippets the same as it is in SSMS?
Regards Jan Potgieter
— My variation: Using
— a subquery.
UPDATE ItemPrice
SET Price = Price / 60
FROM ItemList il
WHERE il.id IN (
SELECT id
FROM ItemList
WHERE Country = ‘USA’
)
We can rewrite update query by two other ways.
1) Without alias of updated table (When we design given query by query designer, it is also converting to following query)
UPDATE ItemPrice
SET Price = Price/60
FROM ItemList il
INNER JOIN ItemPrice ON il.ID = ItemPrice.ID
WHERE Country = ‘USA’
2) IN update syntax, use alias name instead of table name
UPDATE ip
SET Price = Price/60
FROM ItemList il
INNER JOIN ItemPrice ip ON il.ID = ip.ID
WHERE Country = ‘USA’
Hello harsh,
Is it possible instead of updating the price/60 to update a table with the sum of a column? So instead of price/60 have sum(price) with a group by function. I am finding that I cannot use an aggregate function on the set command. I have a workaround at the moment which is to write the sum to a worktable and then update original table. I just thought there might be a shorter way instead of using a worktable.
Thank you kindly,
Susanna
I don’t know if that’s possible. please share if you find something.
Declare @CurrencyRate table(country varchar(50),Rate decimal(18,2))
UPDATE ItemPrice
SET Price = Price/Cr.Rate
FROM ItemList il
INNER JOIN ItemPrice ip ON il.ID = ip.ID
INNER JOIN @CurrencyRate Cr on il.Country=Cr.country
we can also another table CurrencyRate.
I know this is an old post but I was wondering if there was a newer more efficient way to do an update/select in sql server 2014?
I don’t think so. If you find it, please share.
Thank you so much Pinal.