Re: SQL Select question

Hello Rob,

There isn't simple answer to you question. I know some possible solutions for the problem but I can't recommend you best one because I don't know details of you application. I can only give you some ideas for thinking.

1. First of all you question suggests that you database or algorithm has wrong design. May be you can change something. For example add new column in table or create new function to avoid select before update.
2. If you can’t do it you can use lock table operator. It's simple and guarantee that nobody change table during your operation. But nobody could do insert, update or delete in this moment.
3. You can make select for update and save all selected id in array. Then you create loop for this array and update each record. But it could be too bulky if select return many records.

Regards,
Michael
Brainbench MVP for Oracle Programming
http://www.brainbench.com

"Rob Panosh" <rob_panosh_at_asdsoftware.com> wrote in message news:<3e0728c2$1_at_nntp01.splitrock.net>...
> Hello,
>
> The following select statement will select all rows from myTable for UPDATE
> and all other users will be blocked until I close the select statement.
>
> Select * from myTable FOR UPDATE WHERE rowstate = 'Posted'
>
> Now if any new rows are added with rowstate of 'Posted' no other users will
> be able to select them for update. Does anybody know how to get around
> this?
>
> I essentally want the user to be able to lock a set of rows do some
> processing then update them, change rowstate to 'Processed' or is there
> anyway I can detect which rows are locked?
>
>
> Thanks,
> Rob Panosh
Received on Thu Dec 26 2002 - 13:57:19 CET