SQL & PL/SQL

Here is the test case.

create table source(old_val integer, new_val integer)
/ 
create table target(val integer)
/ 
insert into target values(1)
/ 
insert into target values(2)
/ 
insert into target values(3)
/ 
insert into source values(1,4)
/ 
insert into source values(1,5)
/ 
insert into source values(2,6)
/ 
insert into source values(3,7)
/ 
insert into source values(3,8)
/ 
commit
/ 

Now i want to update target.val with source.val and the matching condition is source.old_val = target.val

But source.old_val may have multiple values for a given target.val. You can see that in the example. So i want to order by ROWID of source and pick the first record.

So i wrote a UPDATE.

SQL> update target t
  2     set (val) = (select new_val
  3               from (select new_val, row_number() over(order by rowid) rno
  4                       from source s
  5                      where s.old_val = t.val)
  6              where rno = 1)
  7   where exists(select null
  8             from source s
  9            where t.val = s.old_val)
 10  / 
 
3 rows updated.

and this is what i got.

SQL> select * from target
  2  / 
 
       VAL
----------
         4
         4
         4
 
SQL> select * from source
  2  / 
 
   OLD_VAL    NEW_VAL
---------- ----------
         1          4
         1          5
         2          6
         3          7
         3          8

i was expecting the value to be updated as 4,6,7 or 5,6,8 but not sure why its 4,4,4.

Just trying to understand how this update is being processed. Not sure if i am missing something basic.

NOTE: I am interested in knowing the behavior of this update statement and not an alternative method to solve the issue.

Thanks,

Karthick.