Re: For update issues in subquery

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

--
http://www.freelists.org/webpage/oracle-l