update with inner join, update 2 columns from both tables

Question

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?

Answer 1

From looking at your query a little closer, you have b.Col1 in the UPDATE statement. this is incorrect

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

From UPDATE you can only update 1 table at a time

Answer 2

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

Answer 3

Your statement is "Update A", and you're trying to update a column in table B. You might want to create a view containing the columns in tables A and B, and update that. You could also create triggers for table A - perhaps one that will update the appropriate jointed column in table B.