CODEWITHSUNDEEP
sqloracle-databaseplsqlsql-merge
xpetta
xpetta

Reputation: 718

How do I convert these SQL statements into more efficient statements

I have the following code:

  CURSOR Q1 IS
    SELECT COL1, COL2, COL3, COL4, COL5, COL6 FROM TABLE1;

  CURSOR Q2 IS
    SELECT COL11, COL22, COL33, COL44, COL55, COL66 FROM TABLE2;

  PRESENT BOOLEAN;

   FOR X IN Q1 LOOP
     PRESENT := FALSE;
     FOR Y IN Q2 LOOP
      IF (X.COL3 = Y.COL33) AND (X.COL4 = Y.COL44) THEN
        PRESENT := TRUE;
        EXIT;
      END IF;
     END LOOP;

     IF NOT PRESENT THEN
        UPDATE TABLE_X SET COL_A = 'Y';
        COMMIT;
     END IF;
   END LOOP;

I am not sure if I can convert this code to MERGE statement or anything more efficient, if YES, could anyone please help me in converting this code.

Thanks in advance for your time and help!

Upvotes: 0

Views: 64

Answers (2)

MT0
MT0

Reputation: 168361

You can do it all in a single a UPDATE statement using NOT EXISTS clause:

UPDATE TABLE_X
SET COL_A = 'Y'
WHERE NOT EXISTS (
  SELECT 1
  FROM   TABLE1 x
         INNER JOIN TABLE2 y
         ON ( x.COL3 = y.COL33 AND x.COL4 = y.COL44 )
);

db<>fiddle

Upvotes: 0

VBoka
VBoka

Reputation: 9083

You can create only one cursor and join this two tables. Then if I understood you correctly you can update table_X if there is any data in this cursor. 

declare 

CURSOR Q1 IS
SELECT count(X.COL1)
FROM TABLE1 X
join TABLE2 Y
on X.COL3 = Y.COL33 AND X.COL4 = Y.COL44;

v_Q1 int := 0;

begin

open Q1;
fetch Q1 into v_Q1;
close Q1;

If v_Q1 > 0 then
   UPDATE TABLE_X SET COL_A = 'Y';

end if;

end;
/

Here is a small demo

Upvotes: 1

Related Questions