20 December 2019

Updating one table from another table

This time i want to update a transactional table with reference from master table.

After a little search to this case, i found this method that called correlation update
This is how we can do it:

UPDATE table1 tbl1
   SET (name, desc) = (SELECT tbl2.name, tbl2.desc
                         FROM table2 tbl2
                        WHERE tbl1.id = tbl2.id)
 WHERE EXISTS (
    SELECT 1
      FROM table2 tbl2
     WHERE tbl1.id = tbl2.id )
 

Twiit.. Twiit... Gulp