Jeff Eckermann wrote:
> --- Tom Allison <tallison@tacocat.net> wrote:
>
>>I seemed to remember being able to do this but I
>>can't find the docs.
>>
>>Can I run a sql query to insert new or update
>>existing rows in one query?
>>
>>Otherwise I have to run a select query to see if
>>it's there and then
>>another one to update/insert.
>
>
> This is what you have to do.
>
> This question comes up a lot on the lists. You can
> read endless discussions about it if you want to
> search the archives.
>
> The issue is concurrency, i.e. multiple users
> accessing the data at the same time, and perhaps two
> of them wanting to do the same update-else-insert
> combination at the same time. Then you have the so
> called "race condition", i.e. user1 does a select,
> finds the record does not exist, attempts to insert;
> in between those, user2 inserts the row. So, you now
> either have duplicate data (bad), or user1's insert
> fails because of a unique constraint (also bad,
> because the operation has failed).
>
> The only way to guarantee against this is to lock the
> table for the duration of the exercise, which prevents
> any concurrent access at all. This may be acceptable
> if you have few users, or a low insert/update load,
> but may be a performance killer otherwise.
>
So I have to watch out for transactions on this?
Essentially what I'm trying to do is one of the following two:
if exists update a field to field+1 on one record
if it doesn't exist, insert a row with field = 1