loop through cursor's data and compare for another table's value sql

Question

I have two tables. I want to get desc from Table 2 after following steps:
1. Select name from Table1 where type = 'animal';
2. loop through each names from 1. and check for Table2 i.e o_name = name;
3. Then check if desc exist for that o_name.
4. If desc doesnot exist then insert 'pet' for that record on Table2.

How do I do it? For now I have a cursor that has name from Table1. I am thinking to loop through the cursor's record but beyond that I am not being able to do. Please suggest me:

DECLARE
    CURSOR DATA is
        SELECT name 
        FROM Table1
        where type='animal';    
BEGIN
     FOR C IN DATA LOOP
        // After this what can I do?? I cannot do select into because there will be
        // multiple rows
     END LOOP;   
END;

/

Table1:
id | name  | type
---| ----  | -----
  1| Apple | food
  2| Ball  | game
  3| Cat   | animal
  4| Cow   | animal
  5| Ball  | game

   Table2:
o_name | desc
    ---| ----
  Apple| eat  
    Cat| pet
    Cow|

Why do you need to loop through table1. It is known in advance that name will be `Cat`. So why not just get cats from table2 directly? It is not clear what you are trying to achieve. — Olivier Jacot-Descombes, Sep 07 '16 at 19:47
Usually you would do something like `SELECT a.id, a.name, b.desc FROM table1 a INNER JOIN table2 b ON a.name = b.o_name WHERE a.name = 'Cat' ORDER BY a.id, b.desc`. But as I said before, if you only need to get cats it seems pointless to use table1, unless you need other columns from it. — Olivier Jacot-Descombes, Sep 07 '16 at 19:51
@Olivier I have just updated the question. Please take a look at it. — Rthp, Sep 07 '16 at 20:07
I don't understand - are you looking for help on a SELECT statement (displaying a set of result rows), or are you looking to actually UPDATE the rows in Table2 with the description ('pet' in your example)? These are different questions. — , Sep 07 '16 at 23:10

score 1 · Accepted Answer · answered Sep 07 '16 at 20:24

You still can do this as query and don't need a cursor. Note that databases are optimized for working on sets of records, and that's what SQL queries do. Cursors should only be used if other strategies do not work.

UPDATE Table2
SET "desc" = 'pet'
WHERE
    "desc" IS NULL AND
    o_name IN (SELECT name FROM Table1 WHERE "type" = 'animal')

Note that DESC and TYPE are reserved words in Oracle, therefore I enclosed them in double quotes. Single quotes are used to enclose text literals (strings) in Oracle.

loop through cursor's data and compare for another table's value sql

1 Answers1