Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
What is update operation on the cursor having JOIN between 2 tables?
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?
Solution
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.
Example
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.
577 Views
