How to populate column data in one table based on an id match from another table (using the data from the matched table)

Question

In Oracle I have two tables. Both are populated with data and have a timestamp column.

One table has that column filled with data, the other table doesn't. But I need to get the data from the table that does into the column of the other table based on a match of another column.

Each has 'code' so the timestamp from the one table should only be put in the timestamp of the other table where the codes match.

I've tried cursors etc, but it seems like I'm missing something.

Any suggestions?

Answer 1

It sounds like you want a correlated update. This will update every row of destinationTable with the timestamp_col from sourceTable where there is a match on the code column.

UPDATE destinationTable d
   SET timestamp_col = (SELECT s.timestamp_col
                          FROM sourceTable s 
                         WHERE s.code = d.code )
 WHERE EXISTS( SELECT 1
                 FROM sourceTable s
                WHERE s.code = d.code )