CODEWITHSUNDEEP
oracle-database
Itsik Mauyhas
Itsik Mauyhas

Reputation: 3984

Oracle - update only one row without using rowid

I am trying to update a view table, but only the first result from it. I cannot use rowid - not working on view table. Is there a way to update only the first row? as I said using rowid solutions could not work. select query example:

select addr
from addrView
where (tl = '7' and tr = '2')

returns 4 results, but when using update:

update addrView
set home='current'
where (tl = '7' and tr = '2')

I still want to upadte the first row.

Upvotes: 1

Views: 15343

Answers (1)

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20499

ROWID is a unique identifier of each row in the database.

ROWNUM is a unique identifier for each row in a result set.

You should be using the ROWNUM version, but you will need an ORDER BY to enforce a sorting order, otherwise you won't have any guarantees what is the "first" row returned by your query and you might be updating another row.

update addrView
set home='current'
where (tl, tr) = (
   select tl, tr 
   from (select tl, tr
         from addrView 
         where (tl = '7' and tr = '2')
         order by col_1
             , col_2
             , col_3 etc.
        ) result_set
    where rownum = 1);

But, if you don't care about what data is in the first row returned by your query, then you can use only rownum = 1.

update addrView
set home = 'current'
where (tl = '7' and tr = '2')
    and rownum = 1;

Upvotes: 7

Related Questions