Re: sql syntax help

You need to reverse the order of the update and select, and do both inside a transaction.
e.g. (just psuedo code)
begin tran -- (I come from sql server backgroud. I think this is implicit in Oracle, but I added it just to illustrate) update id_table set id = id + 1 -- increments id and also grabs a lock on that row.
select id from id_table
commit tran -- other processes cannot perform update until transaction committed, so above select is safe.

That's how I'd do it in MS SQL, and I think it would work the same for Oracle. If I'm wrong, hopefully some Oracle guru will correct me.

Josh Miller

Ashwath Kakhandiki <ashwath_at_bluedog.com> wrote in message news:sd5e13gtfja96_at_corp.supernews.com...
> Hi everyone,
>
> I have two SQL query statements like this:
>
> SELECT ID FROM ID_TABLE
>
> set variable id = ID in Java code
>
> UPDATE ID_TABLE SET ID= "+ (id+1) + " WHERE ID=" + (id);
>
> Is there a safe way to do this so that it will succeed
>
> for all callers? It is supposed to return a unique,
>
> increasing number every time it's called.
>
>
>
Received on Fri Mar 17 2000 - 00:00:00 CST