Nrthugu

I have two tables, which are basically the same except the name: one for actual data second one for backup. And what I want to do is to update fields in first one using the content from the other one - checking field by field based on ID.

Let's say it should looks like this:

1. Get the first row.


2. FIRST_TABLE(ID_FT, NAME_FT) and SECOND_TABLE(ID_ST, NAME_ST),


3. If ID_FT == ID_ST then NAME_FT = NAME_ST,


4. Get the next row,


5. Go to point 3 and loop until end of the FIRST_TABLE.

What queries should I use? How iterating through the table in ORACLE/SQL looks like?

You can try to use MERGE statement.

Test data:

SQL> create table FIRST_TABLE (

2 ID_FT number(20),

3 NAME_FT varchar2(200));

4 create table SECOND_TABLE (

5 ID_ST number(20),

6 NAME_ST varchar2(200));

7 insert into FIRST_TABLE (ID_FT,NAME_FT) values (1,null);

8 insert into FIRST_TABLE (ID_FT,NAME_FT) values (2,null);

9 insert into SECOND_TABLE (ID_ST,NAME_ST) values (1,'ST1');

10 insert into SECOND_TABLE (ID_ST,NAME_ST) values (2,'ST2');

11 insert into SECOND_TABLE (ID_ST,NAME_ST) values (3,'ST3');

12 commit;

Query:

SQL> merge into first_table t1

2 using second_table t2

3 on (t1.id_ft = t2.id_st)

4 when matched then update set t1.name_ft = t2. name_st;

5 commit;

6 select * from first_table;



ID_FT       NAME_FT 

---------- ---------- 

  1         ST1

  2         ST2

Also, if you will use MERGE, you can insert data in first_table if it's missing.

SQL> merge into first_table t1

2 using second_table t2

3 on (t1.id_ft = t2.id_st)

4 when matched then update set t1.name_ft = t2. name_st

5 when not matched then insert (t1.id_ft,t1.name_ft) values (t2.id_st,t2.name_st);

6 commit;

7 select * from first_table;



ID_FT       NAME_FT 

---------- ---------- 

  1         ST1

  2         ST2

  3         ST3

I think you just want an update with a correlated subquery:

update first_table ft

    set name_ft = (select t2.name_st from second_table st where ft.ID_FT = st.ID_ST)

    where exists (select t2.name_st from second_table st where ft.ID_FT = st.ID_ST);