Can I use
FETCH FIRST ROW ONLY in a select statement of update query?
Example:
(SELECT SCH.SCHEDULEID FROM ASHOK.INTERVIEWERSCHEDULEMASTER SCH,
ASHOK.RESUMEDETAILS SSS WHERE SCH.INTERVIEWLOCATION
=SSS.CANDIDATELOCATION AND SSS.SCHEDULEID IS NULL AND SSS.REQUIREMENTID
IS NOT NULL FETCH FIRST ROW ONLY)
QUESTION POSED ON: 29 JUN 2004
QUESTION ANSWERED BY:
Craig Mullins
No, you can't do that. The SQL statement you show is fine, but you cannot
put the WHERE clause from that SQL statement onto an UPDATE instead of the
SELECT.
Many developers to confuse which query options are available to the SELECT
statements they want to code. A DB2 SELECT is broken down into three
groups: select-statement, fullselect, and subselect.
The select-statement is the form of a query that can be directly specified
in a DECLARE CURSOR statement, or prepared and then referenced in a DECLARE
CURSOR statement. It is the thing most people think of when they think of a
SELECT. The select-statement consists of a fullselect, and any of the
following optional clauses: order-by, fetch-first, update, read-only,
optimize-for, isolation and queryno.
A fullselect can be part of a select-statement, a CREATE VIEW statement, or
an INSERT statement. This sometimes confuses folks as they try to put a
FETCH FIRST n ROWS clause or an ORDER BY in a view or as part of an INSERT.
That's not allowed! I recently had a conversation with a guy who swore that
at one point he created a view using the WITH UR clause and that it worked.
In fact, a fullselect does not allow any of the following clauses: ORDER BY,
FOR READ ONLY, FOR FETCH ONLY, FOR UPDATE OF, OPTIMIZE FOR, WITH, QUERYNO,
and FETCH FIRST. A fullselect specifies a result table - and none of these
afore-mentioned clauses apply.
Finally, a subselect is a component of the fullselect. A subselect specifies
a result table derived from the result of its first FROM clause. The
derivation can be described as a sequence of operations in which the result
of each operation is input for the next.
This is all a bit confusing. Think of it this way: in a subselect you
specify the FROM to get the tables, the WHERE to get the conditions, GROUP
BY to get aggregation, HAVING to get the conditions on the aggregated data,
and the SELECT clause to get the actual columns. In a fullselect you add in
the UNION to combine subselects and other fullselects. Finally, you add on
any optional order-by, fetch-first, update, read-only, optimize-for,
isolation and queryno clauses to get the select-statement.
The UPDATE statement only specifies a search condition. A search condition
is part of the subselect in a select-statement.
Hope that helps! For More Information
Related Topics:
Search Advice from more than 250 TechTarget Experts
Your question may have already been answered! Browse or search more than 25,000 question and answer pairs from more than 250 TechTarget industry experts.
Browse our Expert Advice
ASSOCIATED RSS FEEDS
 |
|
Expert Answer Center: Expert Q&As |
|
View all RSS Feeds | RSS Info
|
|
|
