update query with join on two tables

Question

I have customer and address tables   Query   SELECT   FROM addresses a       customers b WHERE a id   b id   returns 474 records  For these records  I d like to add the id of customer table into cid of address table   Example  If for the first record the id of customer is 9 and id of address is also 9 then i d like to insert 9 into cid column of address table    I tried   UPDATE addresses a         customers b SET a cid   b id WHERE a id   b id   but this does not seem to work

User · Accepted Answer

this is Postgres UPDATE JOIN format   UPDATE address  SET cid   customers id FROM customers  WHERE customers id   address id   Here s the other variations  http   mssql-to-postgresql blogspot com 2007 12 updates-in-postgresql-ms-sql-mysql html

User · Answer

update addresses set cid id where id in  select id from customers

User · Answer

Using table aliases in the join condition   update addresses a set cid   b id  from customers b  where a id   b id

User · Answer

Try this one  UPDATE employee  set EMPLOYEE MAIDEN NAME       SELECT ADD1     FROM EMPS     WHERE EMP CODE EMPLOYEE EMP CODE   WHERE EMPLOYEE EMP CODE  gt   00   AND EMPLOYEE EMP CODE  lt   ZZ

User · Answer

Officially  the SQL languages does not support a JOIN or FROM clause in an UPDATE statement unless it is in a subquery  Thus  the Hoyle ANSI approach would be something like  Update addresses Set cid                 Select c id             From customers As c             where c id   a id               Where Exists                      Select 1                 From customers As C1                 Where C1 id   addresses id                     However many DBMSs such Postgres support the use of a FROM clause in an UPDATE statement  In many cases  you are required to include the updating table and alias it in the FROM clause however I m not sure about Postgres   Update addresses Set cid   c id From addresses As a     Join customers As c         On c id   a id

[sql] update query with join on two tables

Examples related to sql

Examples related to postgresql