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

Stop praising firefighting (in software).

There’s a misunderstanding of what emotional unavailability looks like in action.

Beginner’s guide to SQL

What is git cherry-pick & .gitignore file

Pydantic validators v.s. custom data type

Creating Android App Shortcuts in Launcher

Backup and restore cockroach DB

The Duality of Fmab

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

Using the CASE Statement in SQL Server

CS371p Spring 2022: Week 2

SAP GLM Integration with EWM in S4 Embedded System

Why You Should Migrate SQL Server to Azure