I am trying to think of a trick way to handle something. I have been
successful in using the multiple inserts in one row by using the
,(xx,xx,xx),(xx,xx,xx),(xx,xx,xx) in a values block. BUT Is it possible to
insert if not there for example


Update xxxx
if anyone not found then insert new with same criteria as update
Where region_id in (2,3,4,5,6)



Thanks

Steffan

---------------------------------------------------------------
T E L 6 0 2 . 7 9 3 . 0 0 1 4 | F A X 6 0 2 . 9 7 1 . 1 6 9 4
Steffan A. Cline
Steffan@ExecuChoice.net Phoenix, Az
http://www.ExecuChoice.net USA
AIM : SteffanC ICQ : 57234309
YAHOO : Steffan_Cline MSN : steffan@hldns.com
GOOGLE: Steffan.Cline Lasso Partner Alliance Member
---------------------------------------------------------------

try REPLACE
http://dev.mysql.com/doc/refman/5.1/en/replace.html

-afan

Steffan A. Cline wrote:
> I am trying to think of a trick way to handle something. I have been
> successful in using the multiple inserts in one row by using the
> ,(xx,xx,xx),(xx,xx,xx),(xx,xx,xx) in a values block. BUT Is it possible to
> insert if not there for example
>
>
> Update xxxx
> if anyone not found then insert new with same criteria as update
> Where region_id in (2,3,4,5,6)
>
>
>
> Thanks
>
> Steffan
>
> ---------------------------------------------------------------
> T E L 6 0 2 . 7 9 3 . 0 0 1 4 | F A X 6 0 2 . 9 7 1 . 1 6 9 4
> Steffan A. Cline
> Steffan@ExecuChoice.net Phoenix, Az
> http://www.ExecuChoice.net USA
> AIM : SteffanC ICQ : 57234309
> YAHOO : Steffan_Cline MSN : steffan@hldns.com
> GOOGLE: Steffan.Cline Lasso Partner Alliance Member
> ---------------------------------------------------------------
>
>
>
>

On Dec 16, 2007 10:29 AM, Afan Pasalic <afan@afan.net> wrote:
> try REPLACE
> http://dev.mysql.com/doc/refman/5.1/en/replace.html
>
> -afan
Replace will not do an update. All previous data will be lost.

A mysql-centric alternative solution would be to use INSERT ... ON
DUPLICATE KEY UPDATE
http://dev.mysql.com/doc/refman/5.0/...duplicate.html

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

INSERT ... ON DUPLICATE KEY UPDATE:

http://dev.mysql.com/doc/refman/5.0/...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
>