Update set query from select multiple table

Question

I have 2 table, example table1 and table2. table1 have columns: id, status_new. table2 have columns: id, status. for example, table1 have 1 record and table2 have 3 records, If I join this two table based on id, the record only one but if I query:

UPDATE epolicy.table2
SET status = 'COMPLETED'
FROM epolicy.table2 t2, epolicy.table1 t1
WHERE t1.status_new = 'Bounce' and t2.status = '' and t1.id = t2.id;

The record updated to 'COMPLETED' are 3 rows (all record in table2). Why? because the match record only 1 (based on id) after join the table. For your information, I using postgresql.

Thank you.

Gordon Linoff Gordon Linoff · Accepted Answer · 2018-08-01T10:43:33

Don't repeat the table being updated in the FROM:

UPDATE epolicy.table2 t2
    SET status = 'COMPLETED'
    FROM epolicy.table1 t1
    WHERE t1.status_new = 'Bounce' AND
          t2.status = '' AND
          t1.id = t2.id;

The second reference causes a cross join, which is not what you intend.

Update set query from select multiple table

1 Answers