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.
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
)