Re: SQL - the FOR in a SELECT statment

jal_at_esc188.expansion.com wrote:
>
> Hello,
>
> There is a select statement with a FOR in it which I do not know what it is for.
> Can anyone help. Here is a sample:
>
> select no
> , act
> , status
> , ref1
> , ref2
> into cur_act
> from act
> where no = act_no
> for update of status;
>
> Thanks,
>
> Jason

The 'for update' clause locks the rows selected by the query, so that other sessions trying to lock those rows will have to wait or will get an error message (depending on the nowait clause: select ... for update of column nowait).
'of status' can be used to specify which table to lock, when 'for update' is used in a join:

select a.column1,b.column2
from table1 a, table2 b
where a.key = b.key
for update of a.column1

The previous statement will lock table1, not table2. If you write:

select a.column1,b.column2
from table1 a, table2 b
where a.key = b.key
for update

rows of both tables will be locked

Note that you can find this also in the manuals.

Marc Billiet Received on Wed Sep 16 1998 - 00:31:00 CDT