Search code examples
sqldatabasefor-loopplsqldeveloper

sql for loop query to normal query


I have a for loop query and i am trying to change it without looping and trying to one record to validate with out looping.

so, i am looking for normal query without looping.

<query name="validate2">
    <![CDATA[
    begin
        for rec in (
            select staging.profileId as sfId                    
            from staging_pricematch_adj staging
                left outer join client cl
                    on staging.profileId = cl.salesforce_Id
            where staging.rejectcode is null
                and cl.salesforce_Id is null
        ) loop
            update staging_pricematch_adj
            set rejectcode        = '002',
            rejectReason = 'INVALID_PROFILE_ID'
            where profileId = rec.sfId;             
        end loop;
    end;
    ]]>
</query>

i am looking for noraml query without looping conditions.


Solution

  • Simplistically, this will work:

          update staging_pricematch_adj
          set rejectcode        = '002',
          rejectReason = 'INVALID_PROFILE_ID'
          where profileId IN (
    
            select staging.profileId                    
            from staging_pricematch_adj staging
                left outer join client cl
                    on staging.profileId = cl.salesforce_Id
            where staging.rejectcode is null
                and cl.salesforce_Id is null
    
          )