469,915 Members | 2,697 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,915 developers. It's quick & easy.

Insert/update

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.

Cheers,

Treefrog

Mar 28 '06 #1
4 1364
if (mysql_num_rows($query)) {
UPDATE....
} else {
INSERT
}

if ther is a record mysql_num_rows returns positive value.

Mar 28 '06 #2
Alkimake wrote:
if (mysql_num_rows($query)) {
UPDATE....
} else {
INSERT
}

if ther is a record mysql_num_rows returns positive value.


Indeed, but which way is better?

Both methods will always involve two queries, but is it quicker to try
to UPDATE and fail (because of no rows) or SELECT and find 1 or 0, then
act upon it?

Mar 28 '06 #3
On 28 Mar 2006 01:21:18 -0800, "Treefrog" <in**@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 )
Mar 28 '06 #4
Treefrog 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.

Cheers,

Treefrog

You would probably be better changing the test to be
if( mysql_affected_rows() != 1 )

I have seen situations where the update has failed and affected rows
returns -1, not 0.

-david-

Mar 28 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

16 posts views Thread by Philip Boonzaaier | last post: by
16 posts views Thread by robert | last post: by
20 posts views Thread by Mark Harrison | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.