user2948533 user2948533 - 1 month ago 8
SQL Question

Oracle PL/SQL Script to update column values based on specific condition

I have a table which is having 3 columns-PID,LOCID,ISMGR. Now in existing scenario, for some person, based on the location ID, he is set as ISMGR=true.
But as per the new requirement, we have to make all the ISMGR=true for any person who is having at least one ISMGR=true(means if he is mangager for any one location, he should be manager for all the locations).
I need to do it in Oracle PL SQL.
Table Data before running the script:

PID|LOCID|ISMGR
1 1 1
1 2 0
1 3 0
2 1 0
2 2 1


Table Data after running the script:

PID|LOCID|ISMGR
1 1 1
1 2 1
1 3 1
2 1 1
2 2 1


Any help will be highly appreciated..

Thanks in advance.

Answer

I would be inclined to write this using exists:

update t 
    set ismgr = 1
    where ismgr = 0 and
          exists (select 1 form t t2 where t2.pid = t.pid and t2.ismgr = 1);

exists should be more efficient than doing a subquery with an aggregation.