overwrite_first_rows_in_t1_with_t2_rows

View All Scripts Login to Run Script

Script Name overwrite_first_rows_in_t1_with_t2_rows
Description Supporting script for https://stackoverflow.com/questions/45290252/how-to-update-value-in-one-table-by-coping-it-from-another-table-in-plsql?noredirect=1#comment77555957_45290252
Area SQL General / Data Manipulation
Contributor Boneist
Created Tuesday July 25, 2017

Statement 1

create table f (id number(2), 
                name varchar2(10))

Table created.

Statement 2

create table b (id number(2), 
                name varchar2(10))

Table created.

Statement 3

insert into f (id, name) 
select 1 id, 'Thom' name from dual union all 
select 2 id, 'Bob' name from dual union all 
select 3 id, 'Steven' name from dual union all 
select 5 id, 'Arthur' name from dual

4 row(s) inserted.

Statement 4

insert into b (id, name) 
select 7 id, 'Nataly' name from dual union all 
select 9 id, 'Alfred' name from dual

2 row(s) inserted.

Statement 5
```
commit
```
```
Statement processed.
```

Statement 6

Running the merge when the target table has more rows than the source.

merge into f tgt  
  using (select coalesce(b1.id, f1.id) id,  
                coalesce(b1.name, f1.name) name,  
                f1.f_rowid  
         from   (select id,  
                        name,  
                        rowid f_rowid,  
                        row_number() over (order by id) rn  
                 from   f) f1  
                full outer join (select id,  
                                        name,  
                                        row_number() over (order by id) rn  
                                 from   b) b1  
                  on f1.rn = b1.rn) src  
    on (tgt.rowid = src.f_rowid)  
when matched then  
  update set tgt.id = src.id,  
             tgt.name = src.name  
  where tgt.id != src.id  
  or    tgt.name != src.name  
when not matched then  
  insert (tgt.id, tgt.name)  
  values (src.id, src.name)

2 row(s) updated.

Statement 7
```
commit
```
```
Statement processed.
```

Statement 8

select * from f


ID NAME
7 Nataly
9 Alfred
3 Steven
5 Arthur

4 rows selected.

Statement 9
```
drop table b
```
```
Table dropped.
```
Statement 10
```
drop table f
```
```
Table dropped.
```

ID	NAME
7	Nataly
9	Alfred
3	Steven
5	Arthur

Statement 11

create table f (id number(2), 
                name varchar2(10))

Table created.

Statement 12

create table b (id number(2), 
                name varchar2(10))

Table created.

Statement 13

insert into f (id, name) 
select 1 id, 'Thom' name from dual

1 row(s) inserted.

Statement 14

insert into b (id, name)  
select 7 id, 'Nataly' name from dual union all  
select 9 id, 'Alfred' name from dual union all  
select 10 id, 'Fred' name from dual

3 row(s) inserted.

Statement 15
```
commit
```
```
Statement processed.
```

Statement 16

Running the merge when the target table has fewer rows than the source.

merge into f tgt  
  using (select coalesce(b1.id, f1.id) id,  
                coalesce(b1.name, f1.name) name,  
                f1.f_rowid  
         from   (select id,  
                        name,  
                        rowid f_rowid,  
                        row_number() over (order by id) rn  
                 from   f) f1  
                full outer join (select id,  
                                        name,  
                                        row_number() over (order by id) rn  
                                 from   b) b1  
                  on f1.rn = b1.rn) src  
    on (tgt.rowid = src.f_rowid)  
when matched then  
  update set tgt.id = src.id,  
             tgt.name = src.name  
  where tgt.id != src.id  
  or    tgt.name != src.name  
when not matched then  
  insert (tgt.id, tgt.name)  
  values (src.id, src.name)

3 row(s) updated.

Statement 17
```
commit
```
```
Statement processed.
```

Statement 18

select * from f


ID NAME
7 Nataly
9 Alfred
10 Fred

3 rows selected.

Statement 19
```
drop table b
```
```
Table dropped.
```
Statement 20
```
drop table f
```
```
Table dropped.
```

ID	NAME
7	Nataly
9	Alfred
10	Fred

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database