PL/SQL Update query with where condition as a select query with some null values

Question

I want to update a table and I have written a pl/sql for it, I use a cursor for select query, the data from the select query is used as the valid value for a update query. The problem is the select query may some times return NULL as a valid value and the escape sequence for loop(cursor) is when null encountered. How do I run update query for when the select query returns some value and skip when it returns null and then end the cursor when there is no more data left.

The select statement is something like this

select phone_number from customers where last_name = 'SINGH';

now there may be some customers that haven't provided their phone_numbers

and the update query is inside a loop of the cursor

DECLARE   
   p_no customers.phone_number%type;  
   CURSOR t_lcpc is  
      select phone_number from customers where last_name in('SINGH', 'RATHORE', 'GUPTA','KUMAR','JAIN');  
BEGIN  
   OPEN t_lcpc;  
   LOOP  
      FETCH t_lcpc into  p_no;  
      EXIT WHEN t_lcpc%notfound;  
      execute immediate q'[update deals set country = "INDIA" where phone_number = :var1]' using p_no;  
   END LOOP;  
   CLOSE t_lcpc;  
END;

the escape sequence i usually use is EXIT WHEN t_lcpc%notfound; but if in case the select query doesn't return any value(for say 'GUPTA') then it will exit without updating for the rest of the last_names.

for tables customer

First_Name  |  Last_Name  |  Phone_Number |  Book

 ATUL       |  SINGH      |  3241234      |   'Let Us C'
 Aaman      |  RATHORE    |  431343       |   'Cook Book for dummies'
 Rahul      |  GUPTA      |  (null)       |   'Network Security'
 Rajnish    |  KUMAR      |  234345       |   'guide to Australia'
 Baibhav    |  JAIN       |  123234       |   'TT for dummies'

I want the deals table to get updated like


Phone_Number |  Country   | Offers 

3241234      |  INDIA     |   new books  
431343       |  INDIA     |   new books 
234345       |  INDIA     |   new books 
123234       |  INDIA     |   new books

the script should update only when select query returns a value and skip when it returns (null) and the cursor should end when the select statement has no more data. I am running this script on Oracle engine.

I am not that good in Oracle but why don't you just replace the 'EXIT WHEN t_...' by an if p_no is not null then execute ... ? or in the cursor statement just restrict to not null value ? or order the value in your statement with the nulls last ? — Jaisus
– Jaisus, Commented Sep 18, 2019 at 10:08
Some sample data would help a lot here. I don't understand what you are saying about the loop exiting early. The exit condition (not "escape sequence" by the way) only considers the cursor state, not your update, so if you are saying it is exiting early due to the update then I can't see how that could happen with the code as posted. — William Robertson
– William Robertson, Commented Sep 18, 2019 at 10:16

Arif Sher Khan · Accepted Answer · 2019-09-18 10:17:57Z

2

I understand that, in Cursor t_lcpc you should select only those where phone_number is not null.

That would do the trick and you will get only those who have phone_number filled in.

And just curious that why are you using procedure, when you can do that with simple update statement !

answered Sep 18, 2019 at 10:17

Arif Sher Khan

6096 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

amar2108 Over a year ago

actually this is a sample example, i have a lot of values that i need to insert into the select condition. and the i have to update other tables as well if condition is met.

KnApP993 · Accepted Answer · 2019-09-18 10:14:23Z

1

in my opinion you could use something like this:

BEGIN
    FOR r IN ( SELECT phone_number FROM customers WHERE last_name IN ( 'SINGH', 'RATHORE', 'GUPTA', 'KUMAR', 'JAIN' ))
    LOOP
        IF ( r.phone_number IS NOT NULL ) THEN
            EXECUTE IMMEDIATE q'[update deals set country = "INDIA" where phone_number = :var1]'
                USING r.phone_number;
        END IF;
    END LOOP;
END;

answered Sep 18, 2019 at 10:14

KnApP993

763 silver badges13 bronze badges

Comments

Goran Kutlaca · Accepted Answer · 2019-09-18 10:13:06Z

0

The simplest way I see is using UPDATE statement with JOIN on phone_number, like this:

UPDATE ( SELECT deals.country as OLD, 'INDIA' as NEW
           FROM deals 
           JOIN customers
             ON deals.phone_number = customers.phone_number
          WHERE customers.last_name IN ('SINGH', 'RATHORE', 'GUPTA','KUMAR','JAIN'....)
            AND deals.country != 'INDIA'
        ) t
SET t.OLD = t.NEW

I hope I helped!

answered Sep 18, 2019 at 10:13

Goran Kutlaca

2,0241 gold badge14 silver badges18 bronze badges

Collectives™ on Stack Overflow

PL/SQL Update query with where condition as a select query with some null values

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related