Posted by David Mackenzie on 11/18/17 11:43

On 28 Mar 2006 01:21:18 -0800, "Treefrog" <info@designstein.co.uk>
wrote:

>Hi all,
>
>I came accross a little piece of code today that I thought was quite
>cute, but I'm not sure if it's any better than my usual method. The
>code ultimately needs to see if a unique identifier exists in a
>database, then either insert or update accordingly.
>
>The way I've always done it is:
>
>SELECT * FROM blah where.....
>
>if (there's a row) then
> UPDATE.....
>else
> INSERT
>
>but the nifty bit of code did it like this.
>
>UPDATE....
>if (mysql_affected_rows == 0) then
> INSERT
>
>Which I presume if more efficient, but I'd like other people opinions
>please.

The first way always executes two queries, whereas the second way may
do one or two.

If an UPDATE is required most of the time, the second way will usually
just do the one query required so it is more efficient.

However, if an INSERT is required most of the time, the second way
will do two queries which is not as efficient. In this case I'd do the
INSERT first and then an UPDATE if MySQL reports a primary key
violation.

--
David ( @priz.co.uk )

[Back to original message]