updating a column in one table with value extracted from other table

Question

I select email across two tables as follows:

select email 
from table1 
inner join table2 on table1.person_id = table2.id and table2.contact_id is null;

Now I have a column in table2 called email

I want to update email column of table2 with email value as selected above.

Please tell me the update with select sql syntax for POSTGRES

EDIT:

I did not want to post another question. So I ask here:

The above select statement returns multiple rows. What I really want is:

update table2.email 
    if table2.contact_id is null with table1.email
    where table1.person_id = table2.id

I am not sure how to do this. My above select statement seems incorrect.

Please help.

I may have found the solution:

Update a column of a table with a column of another table in PostgreSQL

If you "found a solution", you can post it here as an answer and mark it as accepted. — Racil Hilan, Mar 20 '14 at 07:39
I was looking for following solution. http://stackoverflow.com/questions/13473499/update-a-column-of-a-table-with-a-column-of-another-table-in-postgresql?rq=1 — GJain, Mar 20 '14 at 16:17

score 1 · Answer 1 · answered Mar 12 '14 at 03:24

1

Have you tried something like:

UPDATE table2 SET
email = (SELECT email 
         FROM table1 
         INNER JOIN table2 ON table1.person_id = table2.id AND table2.contact_id IS NULL)
WHERE ...

answered Mar 12 '14 at 03:24

Racil Hilan

24,690
13
50
55

score 1 · Answer 2 · answered Mar 12 '14 at 03:25

UPDATE Table2
     SET  email = 
            (SELECT 
                 email
             FROM 
                 table1 
              JOIN 
                  table2 
               ON 
                  table1.person_id = table2.id 
               WHERE 
                   table2.contact_id is null) dt
      WHERE 
         <<SOME CONDITION THAT ISOLATES WHICH ROWS YOU WANT TO UPDATE >>

score 1 · Accepted Answer · edited May 23 '17 at 11:43

1

I was looking for following solution.

Update a column of a table with a column of another table in PostgreSQL

UPDATE table2 t2

SET    val2 = t1.val1
FROM   table1 t1
WHERE  t2.table2_id = t1.table2_id
AND    t2.val2 IS DISTINCT FROM t1.val1  -- to avoid empty updates

edited May 23 '17 at 11:43

Community

1
1

answered Mar 20 '14 at 16:19

GJain

5,025
6
48
82

updating a column in one table with value extracted from other table

3 Answers3