Update rows selected by join in Oracle database

Question

I have the following query, in which I try to put everything so that I only need to execute the query once for optimization (oracle 12c DBMS).

update PersonStatus s
set s.status = 4
where exists (
    select 1 from PersonStatus s1
      inner join Person p
        on s1.id = p.id
    where p.details = 'california' and s1.status = 0 and s1.age in (1,2,3,4,5)
)

Tables:

Person (id, details)
PersonStatus(id, status, age)

where id in PersonStatus references id in Person. Note that I simplified/renamed the tables just for demo purposes.

Basically, I just want to update the rows that match the condition in where exists (....), but I didn't seem to get it.

When I execute this, it updated all the rows in the table, but what I need is to update only those rows that have age in the given list (1,2,3,4,5). This is for Oracle 12c database.

Any idea why the behavior is like that? Suggestions are appreciated.

===== in MySQL. my following query works fine:

update PersonStatus s
 inner join Person p on s.id = p.id
 set s.status = 4;
 where p.details = 'california' and s.status = 0 and s.age in (1,2,3,4,5)

I try to achieve this in Oracle 12c.

score 1 · Answer 1 · answered Dec 22 '15 at 22:10

1

I think the below modified query should work for you

update PersonStatus s
   set s.status = 4
 where exists (select 1
                 from Person p
                where s.id = p.id
                  and p.details = 'california'
             )
  and s.status = 0
  and s.age in (1,2,3,4,5)
;

answered Dec 22 '15 at 22:10

vmachan

1,672
1
10
10

it works, indeed. Extra question: can we use join to improve the efficiency, instead of using a select sub-query? – Simo Dec 23 '15 at 00:31
look at this [SO post] (http://stackoverflow.com/questions/2446764/update-statement-with-inner-join-on-oracle) for how to use the MERGE statement in Oracle to join tables for updating data. In terms of efficiency, since you are using an EXISTS clause it might not be that bad, you would have to try both and compare. Would be great if you could post your comparison results here. – vmachan Dec 23 '15 at 01:42

score 0 · Answer 2 · answered Dec 22 '15 at 22:00

I think you just want a correlated subquery. In other words, you don't need PersonStatus in the subquery:

update PersonStatus s
    set s.status = 4
    where exists (select 1
                  from Person p
                  where s.id = p.id and
                        p.details = 'california' and
                        s.status = 0 and s.age in (1,2,3,4,5)
                 );

I am guessing that this is the logic you are looking for.

Update rows selected by join in Oracle database

2 Answers2