[SQL] Update with two tables?

Question

I am trying to update table A with data from table B. I thought I could do something like:

 UPDATE A
 SET A.name = B.name
 WHERE A.id = B.id

but alas, this does not work.

Anyone have an idea of how I can do this?

Answer 1

Your query does not work because you have no FROM clause that specifies the tables you are aliasing via A/B.

Please try using the following:

UPDATE A
    SET A.NAME = B.NAME
FROM TableNameA A, TableNameB B
WHERE A.ID = B.ID

Personally I prefer to use more explicit join syntax for clarity i.e.

UPDATE A
    SET A.NAME = B.NAME
FROM TableNameA A
    INNER JOIN TableName B ON 
     A.ID = B.ID

Answer 2

For Microsoft Access (don't laugh!)...

UPDATE TableA A
    INNER JOIN TableB B
    ON A.ID = B.ID
SET A.Name = B.Name