Rthp Rthp - 3 months ago 9
SQL Question

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

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|

Answer

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.