Update using internal 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

Failure with the message "Invalid column name b.col1."

How do I do this job?

Answer 1

You can only update one table at a time

you need to release 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 2

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

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 one table at a time

Answer 3

- " A", B. , , A B, . A - , , B.

Answer 4

To update the column value in 1 table based on the condition for another column in the 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')