List:General Discussion« Previous MessageNext Message »
From:Scott Hess Date:October 31 1999 10:08pm
Subject:Re: if exists UPDATE else INSERT
View as plain text  
As someone mentioned, REPLACE works for some stuff, but is often suboptimal
because you'll be replacing _everything_ in the row, even if you don't have
it available to stuff into the REPLACE call.

As a backup for REPLACE in such cases, I use UPDATE, look at the affected
rows value, and if no rows were affected, followup with INSERT (there's a
race condition if someone else is in the same code - if the INSERT fails,
try another UPDATE).  This is tuned for cases where UPDATE is the norm,
with infrequent INSERTs.  If the row tends to _not_ be there, you'd want to
do the INSERT, and if it fails do the UPDATE.  You want to pick the case
which results in fewer queries.

The problem is that UPDATE will return zero affected rows if the WHERE
clause caught no rows, _or_ if the new data exactly matched the existing
data.

Later,
scott

----- Original Message -----
From: Erik Andersson <erik@stripped>
To: MySQL Mailinglist <mysql@stripped>
Sent: Saturday, October 30, 1999 5:28 AM
Subject: if exists UPDATE else INSERT


> Can I create a SQL command which does an UPDATE if a post exist or else
does
> an INSERT?
> I'm trying to build a shopping cart and has run into a problem. A user
that
> is using the shopping cart should have the ability to order a quantity of
a
> product, that is no problem:
>
> mysql> INSERT INTO orderrow (customer_id, product_id, quantity);
>
> But when a user wants to order a product _or_change_the_quantity_ of a
> product (which is made from the same form), I need to check if the
product
> exists in the 'orderrow' table or not. In case that it exists I would do
an
> UPDATE, else I would do an INSERT. Is this possible in _one_ SQL command?
>
> Like:
> mysql> IF ((SELECT COUNT(*) FROM orderrow WHERE product_id = $ID) > 0)
>
> mysql>     UPDATE ....
> mysql> } ELSE {
> mysql>     INSERT ....
> mysql> }
>
>
> Best regards / Erik
>
>
> ---------------------------------------------------------------------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail mysql-thread17380@stripped
>
> To unsubscribe, send a message to the address shown in the
> List-Unsubscribe header of this message. If you cannot see it,
> e-mail mysql-unsubscribe@stripped instead.
>

Thread
if exists UPDATE else INSERTErik Andersson30 Oct
  • Re: if exists UPDATE else INSERTJim Faucette30 Oct
  • Re: if exists UPDATE else INSERTScott Hess1 Nov
    • Re: if exists UPDATE else INSERTVivek Khera1 Nov
  • Re: if exists UPDATE else INSERTScott Hess2 Nov