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
Can we useinner join and Left join in same Update statement?
Hey Dave,
When I do an update with a where clause using “IN (val1, val2)” and I’m using a temporary table, it works fine.
However, when I attempt this with a real table, I get the “Subquery returned more than 1 value…” error.
Thoughts?
can you please check it out, why this query is not working?
UPDATE payments SET payments.booking_code = bookings.booking_code
FROM payments INNER JOIN bookings
ON payments.booking_id = bookings.id
well thanks forsharing but can u change ur column name to something more realistic. it’s so hard to understand what does col1, col2 mean…..
Worked perfectly for me.
UPDATE TABLE_A a INNER JOIN TABLE_B b ON a.col1 = b.col2 SET a.col_which_you_want_update = b.col_from_which_you_update;
UPDATE usertable
SET u.cstage=(u.cstage+1) , u.cday=1
FROM usertable u
INNER JOIN crop c ON u.cropid=c.cropid
WHERE u.cstage<=c.stage AND u.userid=1
GO
i got error in this plz help me
How about if I need to do joins and aliasses to update value? Like if my select statement is like that:
SELECT wp_posts.ID ,wp_posts.post_title
AS Name ,wstock.meta_value
AS Stock ,wsku.meta_value
AS SKU FROM wp_posts
LEFT JOIN wp_postmeta
AS wstock ON wp_posts.ID = wstock.post_id
and wstock.meta_key=’_stock’
LEFT JOIN wp_postmeta
AS wsku ON wp_posts.ID = wsku.post_id
AND wsku.meta_key=’_sku’
WHERE post_type = ‘product’
AND wstock.meta_value != 100
AND wstock.meta_value IS NOT NULL
AND wsstock.meta_value > @stock;
And I want to change it a way that it updates higher stock value to be as same as @stock.
I have found some examples about that but I’m not very pleased for those. I have tried to find also somekind a documentation about that but no good result. If you know where to find one please inform me.