SQL to fill columns based on columns of associated table

Question

Suppose I have

• table first_table which has an FK to table second_table
• table second_table which has column called name_field

Now, I want to add a column in first_table called name_field and fill it with the one on the associated second_table.

How should I fill the values purely using SQL?

(this is Oracle, if that matters)

Answer 1

You can make a trigger in the FK table to do so automaticaly:

UPDATE table1
SET <field> = (select <field> from inserted where id=table1.id)

Answer 2

update (select first_table.name_field nf1,
               second_table.name_field nf2
          from first_table,
               second_table
          where ... (join condition) ...
        )
set nf1 = nf2

Answer 3

Potentially there are 2 different tasks: 1) initialize values in the new column I think that syntax below is the most universal UPDATE table1 SET <field> = (select <field> from table2 where id=table1.id) 2) synchronize values between 2 column in 2 tables based on the approach of j.a.estevan