Update with inner join, update 2 columns from both tables

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开发者_JAVA百科 b.col1."

How do I make this work?

---

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

---

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

---

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.

---

For updating a column value in 1 table based on a condition on another column in second table, this worked for me:

UPDATE TableA 
SET TableA.col1 = 'dummyVal'
WHERE TableA .ACCID IN (SELECT ACCID FROM TableB WHERE TableB.PRODID LIKE 'XYZ')