DB2 SQL CURSOR : POSITIONED UPDATE & DELETE

Solution 1: Without using POSITIONED UPDATE

DECLARE @C1 CURSOR for 
(Select id,name from EXAMPLETABLE where processedFlag <>'Y')
FETCH @C1 into :localID, :localName
DoW(SQLCODE = 0)
---- PROCESSING LOGIC------

UPDATE EXAMPLETABLE set processedFlag ='Y'
WHERE id =localID and name = localName

FETCH @C1 into :localID, :localName
EndDO

Solution 2: Using POSITIONED UPDATE

DECLARE @C1 CURSOR for 
(Select id,name from EXAMPLETABLE where processedFlag <>'Y')
FOR UPDATE
DECLARE @C1 CURSOR for 
(Select id,name from EXAMPLETABLE where processedFlag <>'Y')
FOR UPDATE OF processedFlag ;
FETCH @C1 into :localID, :localName
DoW(SQLCODE = 0)
---- PROCESSING LOGIC------

UPDATE EXAMPLETABLE set processedFlag ='Y'
WHERE CURRENT OF @C1

FETCH @C1 into :localID, :localName
EndDO
  1. Now, the WHERE clause of the UPDATE statement has “CURRENT OF @C1”. This “CURRENT OF @C1” tells the system to update EXAMPLETABLE based on the current position of the CURSOR @C1.
  2. When CURSOR reads 1st record, it will update only 1st record as CURSOR is pointed on 1st record, 3rd record will not be updated even id and name are duplicate.

POSITIONED DELETE

DELETE FROM EXAMPLETABLE WHERE CURRENT OF @C1
  1. must contain only one base table means no SQL JOINS are allowed
  2. must not contain GROUP BY clause or HAVING clause
  3. must not contain column functions in the select list
  4. must not contain SET operations (UNION, EXCEPT, or INTERSECT)
  5. must not contain DISTINCT
  6. must not contain ORDER BY or FOR READ ONLY clause

--

--

--

A programmer.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

The Importance of Having a Unified Development Environment

How does Symfony perform as compared to other PHP Frameworks?

Jupyter Everywhere

Process synchronization monitors in go

Updates / Changelog February 2022

Introduction to Design Patterns and Power of Singleton Design Pattern

Big O Notation and Time Complexity Part 5

AR Book Map

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Sumit goyal

Sumit goyal

A programmer.

More from Medium

How to Improve ServiceNSW COVID Case Alerts

OMG! DES no longer works in my new D365 solution

Career in Microsoft Power BI

Combining rows with same identifier by using only formula in Excel