How to UPDATE a Table with Values from Other Table in SQL Server

There is an easy way to insert data from one table to another in SQL Server:

How to insert data from one table to another in SQL Server

Sometimes there is a need to update a table using values from another table.

To demonstrate this we will first create a Sales table with some values in it:

CREATE TABLE Sales (ID int, Yr int, SalesValue int)
GO
INSERT INTO Sales VALUES ('1', 2012, 45)
INSERT INTO Sales VALUES ('2', 2013, 30)
INSERT INTO Sales VALUES ('3', 2011, 96)
INSERT INTO Sales VALUES ('4', 2010, 30)
INSERT INTO Sales VALUES ('5', 2010, 22)
INSERT INTO Sales VALUES ('6', 2011, 81)
INSERT INTO Sales VALUES ('7', 2011, 96)
INSERT INTO Sales VALUES ('8', 2013, 66)
GO

Then we will create a SalesTemp table from which we want to update the Sales table:

CREATE TABLE SalesTemp (ID int, Yr int, SalesValue int)
GO
INSERT INTO SalesTemp VALUES ('2', 2011, 92)
INSERT INTO SalesTemp VALUES ('4', 2012, 11)
INSERT INTO SalesTemp VALUES ('8', 2014, 45)
GO

To update all rows in Sales table with values from SalesTemp table that have the same ID and different values in Yr column or SalesValue column:

UPDATE
s
SET
s.Yr = st.Yr,
s.SalesValue = st.SalesValue
FROM
Sales s
INNER JOIN
SalesTemp st
ON
s.ID = st.ID
WHERE
s.Yr != st.Yr or
s.SalesValue != st.SalesValue

Rows in the Sales table are updated with values from SalesTemp table:

UPDATE _table_from_other_table

In SQL Server 2008 and later versions you can do the same using MERGE command:

MERGE INTO Sales s
USING SalesTemp st
ON s.ID = st.ID
AND (s.Yr != st.Yr or
s.SalesValue != st.SalesValue)
WHEN MATCHED THEN
UPDATE
SET
s.Yr = st.Yr,
s.SalesValue = st.SalesValue;

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">