Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: For update issues in subquery
Harvinder,
On second thought, if you reformulate the query this way you get what you want:
SELECT dd
FROM (SELECT dd FROM dd1 WHERE dd = (SELECT MIN(dd) FROM dd1))
WHERE rownum < 2
FOR UPDATE
/
If dd is unique, you could remove the rownum<2 predicate.
Hope that helps.
On 2/14/07, Nigel Thomas <nigel_cl_thomas_at_yahoo.com> wrote:
>
> Harvinder asked:
>
>
>
> >>Is there any way we can use the for update clause in subquery?
>
>
> No. Look at the syntax diagrams here:
>
>
> http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_103a.htm
>
> select ::= subquery [for_update_clause];
> and then all the remaining goodness - NOT including for update - is under
> the definition of subquery
>
> subquery ::=
>
> [ subquery_factoring_clause ]
> *SELECT* [ hint ] [ { *DISTINCT* | *UNIQUE* } | *ALL* ] select_list
> *FROM* table_reference [, table_reference]...
> [ where_clause ]
> [ hierarchical_query_clause ]
> [ group_by_clause ]
> [ *HAVING* condition ]
> [ { *UNION* | *UNION ALL* | *INTERSECT* | *MINUS* } ( subquery )]
> [ order_by_clause ]
>
> And anyway, you wouldn't want to have Oracle potentially lock all the rows
> in your subquery, and then only select the first few by rownum? I guess that
> must just be a simple example.
>
> Why do you need to select for update? Any rows you update will become
> locked anyway; do you have a timing issue? What's the real issue you are
> trying to solve?
>
> For the nitpickers, I think the BNF is inaccurate; you can also have
> brackets around even a single simple subquery; this BNF incorrectly states
> that the brackets can only be (and MUST be) around the second subquery in a
> compound query. This next statement
>
> (((((((((((((select 1 from dual))))))))))))) for update
>
> is a perfectly valid query (try it)...
>
> HTH
>
> Regards Nigel
>
>
>
-- Rumpi Gravenstein -- http://www.freelists.org/webpage/oracle-lReceived on Wed Feb 14 2007 - 18:10:31 CST
![]() |
![]() |