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:[EMAIL PROTECTED] Sent: Friday, April 13, 2007 2:28 AM To: Douglas Pearson Cc: [EMAIL PROTECTED] 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 +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/[EMAIL PROTECTED] > > > -- > 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
