0

I have a db2 database and I want to do the following via a shell script in unix:

(1) run a query

SELECT MEMBERID FROM MEMBERS WHERE AGE<16

(2) then for each record returned by this query, I want to update it in this way:

UPDATE MEMBERS SET ADULT='N' WHERE MEMBERID=<the member id returned above>

I already know how to connect to db2 and run the SELECT and get the value of MEMBERID for processing. However, I can't figure out how loop through a resultset of multiple records returned by the query. Can anyone help me how to go about doing that? How does one loop through a db2 resultset in unix so that this can be done?

heisenbergman
  • 1,459
  • 4
  • 16
  • 33

2 Answers2

0

I don't use db2, but in Oracle it would be

UPDATE MEMBERS 
   SET ADULT='N' 
 WHERE MEMBERID IN (SELECT MEMBERID 
                      FROM MEMBERS
                     WHERE AGE<16)
OTTA
  • 1,071
  • 7
  • 8
0

Why would you want to loop the the results set?

Simply:

UPDATE members
SET adult = 'N'
WHERE age < 16

SQL is a set based language...looping through a results set and running more SQL for each row should be avoided if at all possible. It's a common mistake for people used to writing in procedural languages. There's an acronym to remember when it comes to SQL, RBAR: Row-By-Agonizing-Row

Google SQL RBAR for more information...and be sure to look at the example here RBAR vs. Set based programming for SQL

Charles
  • 21,637
  • 1
  • 20
  • 44