Update with inner join, update 2 columns from both tables

sql, sql-server, sql-update

Solution

You can only update 1 table at a time

you need to issue 2 update statements

UPDATE a SET a.col2='new', a.col3='www.google.com'
FROM tablea a INNER JOIN tableb  b ON a.col1 = b.col1
WHERE a.col1=7

UPDATE b SET b.col1='10' 
FROM tablea a INNER JOIN tableb b ON a.col1 = b.col1
WHERE a.col1=7

Problem

This is my query in sql server 2008 - ``` UPDATE a SET a.col2 = 'new', a.col3 = 'www.google.com', b.col1 = '10' FROM table a INNER JOIN table b ON a.col1 = b.col1 WHERE a.col1 = 7 ``` It crashes stating "Invalid column name b.col1." How do I make this work?

Original source