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?
This question is related to
sql
sql-server
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
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
For Microsoft Access
UPDATE TableA A
INNER JOIN TableB B
ON A.ID = B.ID
SET A.Name = B.Name
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.
It can be as follows:
UPDATE A
SET A.`id` = (SELECT id from B WHERE A.title = B.title)
Source: Stackoverflow.com