FOR FETCH ONLY WITH UR under nested select

[HanbingL]

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.

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

and i got following error:

ERROR [42601] [IBM][DB2/NT] SQL0104N An unexpected token "FETCH FIRST 1 ROWS" was found following "ORDER BY DEPT". Expected tokens may include: "<space>". SQLSTATE=42601

Please let me know if it is possible to do what I was trying to achieve. Thanks in advance!

 

[HanbingL]

I left "FOR" before "READ ONLY WITH UR".
But same problem

 

[Telemachus]

If you are trying not to get the production table locked and to read the latest data in production you need not use the FOR READ ONLY clause. using WITH UR will do the job for you.FOR READ ONLY caluse is used for cursor selects. For more info please refer to:

http://publib.boulder.ibm.com/infoc...topic=/com.ibm.db2.udb.doc/admin/c0004121.htm

Thanks,
Madhu

 

[HanbingL]

Thanks, I had to put WITH UR outside the full select. It worked.

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 ;