CODEWITHSUNDEEP
sqloracle-databaseplsql
amar2108
amar2108

Reputation: 323

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

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.

Upvotes: 0

Views: 2035

Answers (3)

Arif Sher Khan
Arif Sher Khan

Reputation: 585

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 !

Upvotes: 2

KnApP993
KnApP993

Reputation: 76

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;

Upvotes: 1

Goran Kutlaca
Goran Kutlaca

Reputation: 2024

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!

Upvotes: 0

Related Questions