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

User · Answer

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  lt  gt  B x   But this works   UPDATE A INNER JOIN B      ON A name   B name SET A x   1 WHERE A x  lt  gt  B x

User · Answer

For Microsoft Access   UPDATE TableA A     INNER JOIN TableB B     ON A ID   B ID SET A Name   B Name

User · Answer

It can be as follows   UPDATE A  SET A  id     SELECT id from B WHERE A title   B title

User · Answer

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

User · Answer

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

[sql] Update with two tables?

Examples related to sql

Examples related to sql-server