SQL SERVER – UPDATE FROM SELECT Statement with Condition

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.

SQL SERVER - UPDATE FROM SELECT Statement with Condition updatefromselect

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.

SQL SERVER - UPDATE FROM SELECT Statement with Condition updatedselectcol

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)

Quest

SQL Scripts, SQL Server, SQL Table Operation
Previous Post
SQL SERVER – How to Check Snapshot Isolation State of Database
Next Post
SQL SERVER – ​Building Technical Reference Library – Notes from the Field #048

Related Posts

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.

    Reply
  • 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 ?

    Reply
  • 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

    Reply
  • michael (@thomas_wgr)
    September 30, 2014 5:40 pm

    — 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’
    )

    Reply
  • 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’

    Reply
    • Susanna Paul
      March 2, 2017 3:03 am

      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

      Reply
  • 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.

    Reply
  • Kyle Johnson
    May 15, 2017 9:29 pm

    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?

    Reply
  • Thank you so much Pinal.

    Reply

Leave a Reply