List:General Discussion« Previous MessageNext Message »
From:Jay Pipes Date:December 17 2007 5:50pm
Subject:Re: Update but insert if not exist
View as plain text  
INSERT ... ON DUPLICATE KEY UPDATE:

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Cheers,

Jay

J Trahair wrote:
> This is a question I want to know the answer to, as well! Is there any way of
> avoiding looking up a specific record in a table to see if it exists, before deciding
> whether to INSERT INTO or UPDATE, eg:
> 
>     mstrSQL = "SELECT * FROM Shops WHERE ShopReference = '" &
> grd1.TextMatrix(numRowNo, 1) & "'"
>     Set rsRecordset = New ADODB.Recordset
>     gconn.CursorLocation = adUseServer
>     rsRecordset.Open mstrSQL, gconn, adOpenDynamic, adLockOptimistic
>     If rsRecordset.EOF = True Then
>         mstrSQL = "INSERT INTO Shops (ShopNameInFull, ShopReference, TillNumber)
> VALUES (strShopNameInFull, strShopReference, strTillNumber)"       
>         mconn.Execute mstrSQL
>     Else
>         mstrSQL = "UPDATE Shops SET ShopNameInFull = 'strShopNameInFull',
> ShopReference = 'strShopReference', TillNumber = 'strTillNumber' WHERE ShopReference = '"
> & grd1.TextMatrix(numRowNo, 1) & "'"
>         mconn.Execute mstrSQL
>     End If
> 
> 
> ....just thought I'd ask!
> 
> Jonathan Trahair
> 
Thread
Update but insert if not existSteffan A. Cline16 Dec
  • Re: Update but insert if not existAfan Pasalic16 Dec
    • Re: Update but insert if not existRob Wultsch17 Dec
  • Re: Update but insert if not existJ Trahair17 Dec
    • Re: Update but insert if not existJay Pipes17 Dec