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:
CURSOR DATA is
FOR C IN DATA LOOP
// After this what can I do?? I cannot do select into because there will be
// multiple rows
id | name | type
---| ---- | -----
1| Apple | food
2| Ball | game
3| Cat | animal
4| Cow | animal
5| Ball | game
o_name | desc
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')
TYPE are reserved words in Oracle, therefore I enclosed them in double quotes. Single quotes are used to enclose text literals (strings) in Oracle.