Re: Atomic check for row existence and insert if doesn't exist

Ace Jayz wrote: If REPEATABLE READ is in effect, then no other transaction would be able to delete the row between the insert and the select because this would result in different results for the select statement if it were repeated. For example, the first execution of the select might

Re: Atomic check for row existence and insert if doesn't exist

2) - insert into t1 where C=c - if insert fails, select id from t1 where C=c - if insert succeeds, get identity column from last insert, id - insert row with column value of id into second table this would seem to be subject to the row being deleted between the insert attempt and the

Re: Atomic check for row existence and insert if doesn't exist

Bernt M. Johnsen wrote: Ace Jayz wrote (2007-02-09 21:08:44): A couple of obvious approached come to mind: 1) - select * from t1 where C=c for update - if row returned, then get value of identity column, id - if row not returned, insert into t1 values(c), get identity column

Atomic check for row existence and insert if doesn't exist

I've got a table with an identity column, P, as a primary key. This table has another column, C, with a uniqueness constraint. I want to insert a row into the table if no row has a value for C=c, and if a row does exist whose column C=c I want to get the value of the identity column for storage