Problem: Is it possible to update a CURSOR in which we have used JOIN on 2 tables ORDERS and TRANSACTIONS? Why or why not? How can we proceed to UPDATE any of these tables?
Whenever we use JOIN in a cursor on two or more tables (ORDERS and TRANSACTIONS in this case) a temporary table is generated in the virtual memory. However, since this is a temporary table we can fetch data from this table but it is not possible to update this table.
If we want to update any of the tables used in the JOIN then we have to declare a separate cursor for all the tables and an individual logic has to be built in order to update each DB2 table. For example, if we want to create an UPDATE cursor to update the ORDERS table then we will declare the cursor as below.
EXEC SQL DECLARE ORDER_CUR CURSOR WITH HOLD FOR SELECT * FROM ORDERS FOR UPDATE OF ORDER_TOTAL, ORDER_STATUS END-EXEC
However, if we wish to update all the columns of this table then we should not specify any column name in the “FOR UPDATE” OF clause. Similarly, if we want to update the TRANSACTIONS table then we need a separate update cursor as above.