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

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

Answer 3

I was scratching my head, not being able to get John Sansom's Join syntax work, at least in MySQL 5.5.30 InnoDB.

It turns out that this doesn't work.

UPDATE A 
    SET A.x = 1
FROM A INNER JOIN B 
        ON A.name = B.name
WHERE A.x <> B.x

But this works:

UPDATE A INNER JOIN B 
    ON A.name = B.name
SET A.x = 1
WHERE A.x <> B.x

Answer 4

The answers didn't work for me with postgresql 9.1+

This is what I had to do (you can check more in the manual here)

UPDATE schema.TableA as A
SET "columnA" = "B"."columnB"
FROM schema.TableB as B
WHERE A.id = B.id;

You can omit the schema, if you are using the default schema for both tables.