Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

FOR FETCH ONLY WITH UR under nested select

Status
Not open for further replies.

HanbingL

IS-IT--Management
Dec 4, 2007
7
US
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!
 
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:

Thanks,
Madhu
 
Thanks, I had to put WITH UR outside the full select. It worked.
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 ;
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top