This is one of the most interesting questions I keep on getting on this email and I find that not everyone knows about it. In recent times I have seen a developer writing a cursor to update a table. When asked the reason was he had no idea how to use multiple tables with the help of the JOIN clause in the UPDATE statement.
Let us see the following example. We have two tables Table 1 and Table 2.
-- Create table1
CREATE TABLE Table1 (Col1 INT, Col2 INT, Col3 VARCHAR(100))
INSERT INTO Table1 (Col1, Col2, Col3)
SELECT 1, 11, 'First'
UNION ALL
SELECT 11, 12, 'Second'
UNION ALL
SELECT 21, 13, 'Third'
UNION ALL
SELECT 31, 14, 'Fourth'
GO
-- Create table2
CREATE TABLE Table2 (Col1 INT, Col2 INT, Col3 VARCHAR(100))
INSERT INTO Table2 (Col1, Col2, Col3)
SELECT 1, 21, 'Two-One'
UNION ALL
SELECT 11, 22, 'Two-Two'
UNION ALL
SELECT 21, 23, 'Two-Three'
UNION ALL
SELECT 31, 24, 'Two-Four'
GO
Now let us check the content in the table.
SELECT *
FROM Table1
SELECT *
FROM Table2
GO
Now let us see the following image. Our requirement is that we have Table2 which has two rows where Col1 is 21 and 31. We want to update the value from Table2 to Table1 for the rows where Col1 is 21 and 31. Additionally, we want to update the values of Col2 and Col3 only.
When you look at this it looks very simple but when we try to think the solution, I have seen developers coming up with many different solutions for example sometime they write cursor, table variables, local variables etc. However, the easiest and the most clean way is to use JOIN clause in the UPDATE statement and use multiple tables in the UPDATE statement and do the task.
UPDATE Table1
SET Col2 = t2.Col2,
Col3 = t2.Col3
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Col1 = t2.Col1
WHERE t1.Col1 IN (21, 31)
GO
Now let us select the data from these tables.
-- Check the content of the table
SELECT *
FROM Table1
SELECT *
FROM Table2
GO
As you can see that using JOIN clause in UPDATE statement it makes it very easy to update data in one table from another table. You can additionally use MERGE statement to do the same as well, however I personally prefer this method. Let us clean up the clause by dropping the tables which we have created.
DROP TABLE Table1
DROP TABLE Table2
GO
Do let me know if you use any other trick in similar situations. If you do, I would like to learn more about it.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
58 Comments. Leave new
performance wise I liked; (not my! code).. guess you already covered it somewhere..
;WITH CTE
AS (SELECT T1.Col1,
T2.Col1 AS _Col1,
T1.Col2,
T2.Col2 AS _Col2
FROM T1
JOIN T2
ON T1.id = T2.id
/*Where clause added to exclude rows that are the same in both tables
Handles NULL values correctly*/
WHERE EXISTS(SELECT T1.Col1,
T1.Col2
EXCEPT
SELECT T2.Col1,
T2.Col2))
UPDATE CTE
SET Col1 = _Col1,
Col2 = _Col2
from stackoverflow..
Thanks, nice post
Thanks for this! Saved me messing around with a MERGE.
Does not work on linked server. (Both tables are on a linked server).
Oh sorry, it does work on linked server, my mistake. Just as fast/slow as where column in (select…) which was a possiblity for me.
update query using <= symbol is possible
i need to fetch 2nd row value in 4th row ….. how it done using update query
Thank you very much. It’s really helpful for me.
now “UPDATE Table1
SET Col2 = t2.Col2,
Col3 = t2.Col3
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Col1 = t2.Col1
WHERE t1.Col1 IN (21, 31)”
is not verified quary by sql.
YOu coud chask on your phpmyadmins ar here piliapp.com/mysql-syntax-check/
Dear sir i want to update a table which have same name,id,age.in every row,There are only three column(name,id,age) how can I do kindly tell me.
What iS table 1?
its a table.
can we insert data into multiple tables by using join?
Nice explanation and i found some error message as Multipart identifier table1.column1 when i executed sample query using your example.
Can you please let me know what are the cases multipart identifier error message will be shown.
I resolved this by providing the alias name after update statement.
your query::
UPDATE Table1
SET Col2 = t2.Col2,
Col3 = t2.Col3
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Col1 = t2.Col1
WHERE t1.Col1 IN (21, 31)
GO
My Query which executed::
UPDATE t1
SET t1.Col2 = t2.Col2,
t1.Col3 = t2.Col3
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Col1 = t2.Col1
WHERE t1.Col1 IN (21, 31)
GO
update GNR3.Party set Mobile in (select a.Phone from GNR3.Address A inner join GNR3.PartyAddress PA on a.AddressID=pa.AddressRef
inner join GNR3.Party P on p.PartyID=pa.PartyRef)
How can i update different Table Column ????
I m Trying Like below but i have an error
“Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword ‘in’.”
can you help me?
thanks
It works… Thank you so much :)
it works perfectly, it help me to the problem i have.Thanks
thank you so much. really helpful :)
Can you help me with following TSQL. If work correctly, but I am not sure how to write this as a SQL Statement
SELECT dbo.ShipmentBilling.pkBillingId AS IdIn, dbo.Shipment.ShipmentMethod AS UpdateValue INTO #MyTemp
FROM dbo.ShipmentBilling LEFT OUTER JOIN dbo.Shipment ON dbo.ShipmentBilling.fkshipmentId = dbo.Shipment.pkShipmentId
Declare @PkId as int
Declare @NewId as int
While EXISTS (Select Top 1 * from #MyTemp )
BEGIN
SET @PkId = (Select Top 1 IdIn From #MyTemp)
Set @NewId = (Select Top 1 UpdateValue FROM #MyTemp)
UPDATE dbo.ShipmentBilling SET fkShipMethod = @NewId where (pkBillingId = @PkId )
DELETE #MyTemp where IdIn = @PkId
END
DROP TABLE #MyTemp
Excellent! This simplified my attempt at solving this problem
My pleasure.