CODEWITHSUNDEEP
sqlsql-server
Md Nasir Uddin
Md Nasir Uddin

Reputation: 2200

Update with two tables?

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?

Upvotes: 51

Views: 172847

Answers (4)

Nicolas
Nicolas

Reputation: 412

For Microsoft Access 

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

Upvotes: 17

renno
renno

Reputation: 2827

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.

Upvotes: 2

Doochz
Doochz

Reputation: 1059

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

Upvotes: 10

John Sansom
John Sansom

Reputation: 41899

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

Upvotes: 81

Related Questions