Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

SQL one to one update

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:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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.

>Solution :

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;
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading