How to copy columns data to another table with common column data exist on the both tables?

Question

I want to copy data of two columns into another table which have many columns and there is a common column on the both tables.

Here is my tables:

Table1

ID | col1 | col2

1 | 13 | 12

2 | 8 | 3

3 | 7 | 10

Table2
ID | col1 | col2 | col3 | col4

1 | 0 | 0 | 0 | 0

2 | 0 | 0 | 0 | 0

3 | 0 | 0 | 0 | 0

i tried to used this UPDATE query from this question

UPDATE table2 a,table1 b SET 
a.col1 = b.col1,
a.col2 = b.col2,
a.col3 = a.col3 + b.col1,
a.col4 = a.col4 + b.col2
WHERE a.ID = b.ID

but it gives

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'a'.

Anyway is it possible to do that ? sorry if i didn't managed to write tables with a nice way but it seems stackoverflow doesn't create tables.

mysql or sql-server ? you tagged both – Abhik Chakraborty Jun 21 '14 at 18:55 — Abhik Chakraborty, Jun 21 '14 at 18:55

score 2 · Accepted Answer · answered Jun 21 '14 at 18:56

Your syntax looks correct for MySQL, but is better written as:

UPDATE table2 a join
       table1 b
       on a.ID = b.ID
    SET a.col1 = b.col1,
        a.col2 = b.col2,
        a.col3 = a.col3 + b.col1,
        a.col4 = a.col4 + b.col2;

For SQL Server, the syntax is:

UPDATE a
    SET col1 = b.col1,
        col2 = b.col2,
        col3 = a.col3 + b.col1,
        col4 = a.col4 + b.col2
    FROM table2 a join
         table1 b
         on a.ID = b.ID;

The error in SQL Server could have been on the alias after the table or on the a in the set statements.