From: Mogens Melander
Date: April 13 2007 9:28am
Subject: Re: Is it possible to either update or insert in a single query?
List-Archive: http://lists.mysql.com/mysql/206125
Message-Id: <4246.90.184.17.152.1176456483.squirrel@mail.fumlersoft.dk>
MIME-Version: 1.0
Content-Type: text/plain;charset=utf-8
Content-Transfer-Encoding: 8bit

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
+45 40 85 71 38
+66 870 133 224


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=mogens@stripped
>
>
> --
> 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.