List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:June 24 2000 2:22am
Subject:Re: Drivers giving different results
View as plain text  
At 6:47 PM -0700 2000-06-23, Mike Wexler wrote:
>I just looked up this old thread because I am having a problem with some
>old code.
>
>The basic problem is that I would like to update a record, but if it
>doesn't exist I'd like to create it. Back when I was new at SQL I
>thought
>
>$dbh->do(<<EOF);
>REPLACE INTO table SET field1='value', uniqueKey='uniqueVal'
>EOF
>
>would do the job nicely. It didn't take make too long to figure out that
>this would cause field2 to get erased if the record already existed.
>
>So the next attempt was
>
>$sth = $dbh->prepare(<<EOF);
>UPDATE table SET field1='value' WHERE uniqueKey='uniqueVal'
>EOF
>$sth->execute();
>
>if ($sth->rows == 0) {
>     $dbh->do(<<EOF);
>INSERT INTO table SET field1='value', uniqueKey='uniqueVal'
>EOF
>}
>
>But alas if field1 is already equal to 'value' then $sth->rows is equal
>to zero and INSERT fails. Of course I check the result of the insert and

Don't use $sth->rows to determine whether or not the statement
failed.  Check the return value of $sth->execute().  If it's undef,
the statement failed.

>generate an error message. There is also a race condition, if the record
>didn't exist at the time of the UPDATE, but was added before the INSERT.
>
>Any recommendations?
>
>How horrible would it be to have
>
>REPLACEPARTIAL INTO table SET field1='value' WHERE uniqueKey='uniqueVal'
>
>which would atomically on the server:
>
>UPDATE INTO table SET field1='value' WHERE uniqueKey='uniqueVal'
>
>but if the WHERE clause doesn't match any records than it would
>
>INSERT INTO table SET field1='value', uniqueKey='uniqueVal'


Doesn't plain old REPLACE do what you want?

-- 
Paul DuBois, paul@stripped
Thread
Re: Drivers giving different resultsMike Wexler24 Jun
  • Re: Drivers giving different resultsPaul DuBois24 Jun
  • Re: Drivers giving different resultsColin Faber24 Jun
  • Re: Drivers giving different resultsMike Wexler24 Jun
    • Re: Drivers giving different resultsPaul DuBois24 Jun
  • Re: Drivers giving different resultsMike Wexler24 Jun