FOR READ ONLY WITH UR in nested select

HanbingL · #1 (**permalink**) 12-04-07, 16:13

First of all, I am new to DB2 (3 weeks). I want to update a TEST table with PRODUCTION table in a nested select with read only with uncommitted read.

This is what I wanted to do but I got error message. Please help, thanks!

Code:

UPDATE TEST_STAFF S2
SET DEPT = (SELECT DEPT from PRODUCTION_STAFF
                   WHERE JOB = S2.JOB
                   ORDER BY DEPT
                   FETCH FIRST 1 ROWS ONLY
                   FOR READ ONLY WITH UR 
                 );

ARWinner · #2 (**permalink**) 12-04-07, 16:45

That is because the FOR READ ONLY and the WITH UR are not allowed in a full-select. They are only allowed in a select-statement.

Andy

n_i · #3 (**permalink**) 12-04-07, 22:08

----------------------------------

jsharon1248 · #4 (**permalink**) 12-05-07, 09:02

You're attempting to take a round-about way to get the lowest PRODUCTION_STAFF.DEPT value for a given TEST_STAFF.JOB. You'll be better off using a MIN function. Also, since you have the same column names on both tables, I'd recommend qualifying both tables for clarity.

Code:

UPDATE TEST_STAFF S2
SET DEPT = (SELECT MIN(P1.DEPT) from PRODUCTION_STAFF P1
                   WHERE P1.JOB = S2.JOB
                 );

HanbingL · #5 (**permalink**) 12-05-07, 10:57

In SQL Server if I remember it correctly NOLOCK can be used in FULLSELECT. The example I showed is something I came up with. I can change it to ORDER BY ID or any order I needed.

My question is: Is there a way not to get the production table locked and to read the latest data in production.

Thanks.

HanbingL · #6 (**permalink**) 12-05-07, 11:07

Never mind. I found a solution. http://publib.boulder.ibm.com/infoce...n/c0004121.htm
FYI:

Quote:

Uncommitted Read (UR)

For SELECT INTO, FETCH with a read-only cursor, fullselect in an INSERT, row fullselect in an UPDATE, or scalar fullselect (wherever it is used), the Uncommitted Read level allows:

* Any row read during a unit of work to be changed by other application processes.
* Any row changed by another application process to be read, even if the change has not been committed by that application process.

Code:

UPDATE TEST_STAFF S2
SET DEPT = (SELECT DEPT from PRODUCTION_STAFF
                   WHERE JOB = S2.JOB
                   ORDER BY SALARY
                   FETCH FIRST 1 ROWS ONLY                   
                 ) WITH UR ;

Internet Services

Web Development

Digital Marketing

Consumer Tech