sqloracle-databasesql-updatebulk-operations

Multiple Columns and different Join Conditions for Oracle update

I have 2 tables , 1 is location and the Other one is Look up table. I have to look into the look up table for the location values and if they are present mark them as 'Y' and 'N' along with their corresponding values

enter image description here

I have written individual update Statements as below:

**Location1,L1value**

Update Location
set (Location1,L1value) = 
(select UPPER(VAlue),'Y'  from Location_lookup  where  trim(Location1)=Location
where exists (select 1 from Location_lookup  where   trim(Location1)=Location);
commit;

**Location2,value**
Update Location
set (Location2,L2value) = 
(select UPPER(VAlue),'Y'  from Location_lookup  where  trim(Location2)=Location
where exists (select 1 from Location_lookup  where  trim(Location2)=Location);
commit;

Similarly for 3rd flag and value.

Is there a way to write single update for all the three conditions? Reason why I am looking for single update is that I have 10+ million records and I do not want to scan the records three different times. The lookup table has > 32 million records.

Solution

  • Here is a solution which uses Oracle's bulk FORALL ... UPDATE capability. This is not quite as performative as a pure SQL solution but it is simpler to code and the efficiency difference probably won't matter much for 10 million rows on a modern enterprise server, especially if this is a one-off exercise.

    Points to note:

    1. You don't say whether LOCATION has a primary key. For this answer I have assumed it has an ID column. The solution won't work if there isn't a primary key, but if your table doesn't have a primary key you've likely got bigger problems.
    2. Your question mentions setting the FLAG columns "as 'Y' and 'N'" but the required output only shows 'Y' setting. I have included processing for 'N' but see the coda underneath.
    declare
  cursor get_locations is
    with lkup as (
      select *
      from   location_lookup
    )
    select  locn.id
           ,locn.location1
           ,upper(lup1.value)          as l1value
           ,nvl2(lup1.value, 'Y', 'N') as l1flag  
           ,locn.location2
           ,upper(lup2.value)          as l2value
           ,nvl2(lup2.value, 'Y', 'N') as l2flag  
           ,locn.location3
           ,upper(lup3.value)          as l3value
           ,nvl2(lup3.value, 'Y', 'N') as l3flag
    from  location locn
          left outer join lkup lup1 on trim(locn.location1) = lup1.location 
          left outer join lkup lup2 on trim(locn.location2) = lup2.location 
          left outer join lkup lup3 on trim(locn.location3) = lup3.location 
    where lup1.location is not null
    or    lup2.location is not null 
    or    lup3.location is not null;
    
  type t_locations_type is table of get_locations%rowtype index by binary_integer;
  t_locations t_locations_type;
  
begin

  open get_locations;
  
  loop
    fetch get_locations bulk collect into t_locations limit 10000;
    exit when t_locations.count() = 0;
    
    forall idx in t_locations.first() .. t_locations.last()
      update location
      set    l1value = t_locations(idx).l1value 
            ,l1flag  = t_locations(idx).l1flag
            ,l2value = t_locations(idx).l2value 
            ,l2flag  = t_locations(idx).l2flag
            ,l3value = t_locations(idx).l3value 
            ,l3flag  = t_locations(idx).l3flag
      where id = t_locations(idx).id;
                  
  end loop;
  
  close get_locations; 
  
end;
/

    There is a working demo on db<>fiddle here. The demo output doesn't exactly match the sample output posted in the query, because that doesn't the given input data.

    Setting flags to 'Y' or 'N'?

    The code above uses left outer joins on the lookup table. If a row is found the NVL2() function will return 'Y' otherwise it returns 'N'. This means the flag columns are always populated, regardless of whether the value columns are. The exception is for rows which have no matches in LOCATION_LOOKUP for any location (ID=4000 in my demo). In this case the flag columns will be null. This inconsistency follows from the inconsistencies in the question.

    To resolve it: