RE: Is it possible to either update or insert in a single query?

[Douglas Pearson]

Thanks Mogens.  I was aware of REPLACE but it was the non-standard ON
DUPLICATE KEY UPDATE that I was looking for.

Thanks,

Doug

-----Original Message-----
From: Mogens Melander [mailto:mogens@stripped] 
Sent: Friday, April 13, 2007 2:28 AM
To: Douglas Pearson
Cc: mysql@stripped
Subject: Re: Is it possible to either update or insert in a single query?

This part of the manual might be of use to you:

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name [(col_name,...)]
    VALUES ({expr | DEFAULT},...),(...),...

Or:

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    SET col_name={expr | DEFAULT}, ...

Or:

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...

REPLACE works exactly like INSERT, except that if an old row in the table
has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old
row is deleted before the new row is inserted. See Section 13.2.4, "INSERT
Syntax".

REPLACE is a MySQL extension to the SQL standard. It either inserts, or
deletes and inserts. For another MySQL extension to standard SQL - that
either inserts or updates - see Section 13.2.4.3, "INSERT ... ON DUPLICATE
KEY UPDATE Syntax".

Note that unless the table has a PRIMARY KEY or UNIQUE index, using a
REPLACE statement makes no sense. It becomes equivalent to INSERT, because
there is no index to be used to determine whether a new row duplicates
another.


--
Later

Mogens Melander
[phone]
[phone]


On Fri, April 13, 2007 08:14, Douglas Pearson wrote:
> Apologies if this is a dumb question, but is it possible to write a 
> single query that either updates certain columns in a row, or adds an 
> entirely new row if there is none already?
>
> I seem to be running into this a lot, and so far I've solved it by:
> 1) run UPDATE table SET x,y WHERE some row
> 2) if rowsChanged == 0 then run the INSERT
>
> It just feels like there must be a way to do this more efficiently.
>
> Thanks,
>
> Doug
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>
> --
> This message has been scanned for viruses and dangerous content by 
> OpenProtect(http://www.openprotect.com), and is believed to be clean.
>



-- 
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.