Re: Update but insert if not exist

[Jay Pipes]

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]