SQL one to one update-CodePudding

Hi I am very new in Oracle/PLSQL.

I have a question. I have one to one relationship between two tables. In both tables I have a column named col2. In table 1 this column is null, in the second it is filled. I want to copy all data from one table to another.

Example:
Table 1                      Table 2
col1        col2   ...       col1        col2   ...
1           null   ...       1           A      ...
2           B      ...       2           B      ...
3           null   ...       3           C      ...

I want it to be:

Table 1                      Table 2
col1        col2             col1        col2   ...
1           A      ...       1           A      ...
2           B      ...       2           B      ...
3           C      ...       3           C      ...

How can I do that with a Query?

I have tried:

UPDATE table 1 SET col2 = (SELECT col2 FROM table 2 WHERE col1 = '1') WHERE col1 = '1'

But it is only for one record. How can I change this query to make it work for whole table?

Note: I can not change table structures, can not delete or add new columns, can not use scripts.

CodePudding user response：

merge might be a good choice:

merge into table1 a
  using table2 b
  on (a.col1 = b.col1)
when matched then update set
  a.col2 = b.col2;