How to make a "scan" using SQL?

Question

I have the following structure in my database:

• Table tgffin with the columns: CODNAT, AD_CODNAT_OLD
• Table tgfnat with the columns: CODNAT, AD_NAT_OLD

I want to reclaim the value of tgfnat.CODNAT when tgfnat.AD_NAT_OLD = tgffin.AD_CODNAT_OLD and subsequently update tgffin.CODNAT using the value recovered from the previous query.

How can I do this using a single SQL statement?

Answer 1

Resolve with a simple update:

UPDATE tgffin, tgfnat
 SET tgffin.CODNAT = tgfnat.CODNAT
 WHERE tgfnat.AD_NAT_OLD = tgffin.AD_CODNAT_OLD;