SQLite insert if not exists else update [duplicate]

Question

So I saw some similar questions here but I just could not understand them.

My table:

'timestamp' int(30) NOT NULL,
'clientguid' varchar(32) NOT NULL,
'clientip' varchar(32) NOT NULL,
'serverip' varchar(32) NOT NULL)

I have 4 variables

$TimeStamp
$ClientGUID
$ClientIP
$ServerIP

My query should do this: if there is a row with clientguid that matches $ClientGUID:

UPDATE 'mytablename'
SET 'timestamp' = '$TimeStamp','clientip' = '$ClientIP','serverip' = '2.2.2.24'
WHERE 'mytablename'.'clientguid' = '$ClientGUID');

If there is not a row matching that:

INSERT INTO 'mytablename' ('timestamp','clientguid','clientip','serverip') 
VALUES($TimeStamp,'$ClientGUID', '$ClientIP', '$ServerIP');

I don't have to worry about escaping and validating, that has already been done I just need a statement that will accomplish this. :)

or stackoverflow.com/questions/418898/… ......................... — edelwater
– edelwater, Commented Oct 4, 2014 at 15:29
see @edelwater's duplicate suggestion then... doesn't look like you're the first one having asked this one... search is your good friend ;) — benomatis
– benomatis, Commented Oct 4, 2014 at 15:31
edelwater, I saw this question but I'm unsure how to specify which column I want to look for a match on. If I just do this update SQL command, then is it updating the first row with a matching server ip? client ip? I would specifically like to do it by client GUID. — dotNNJ
– dotNNJ, Commented Oct 4, 2014 at 15:32

cvrcek · Accepted Answer · 2014-10-04 17:44:57Z

0

If you set unique key on your table, then you can first do it insert and if error duplicity record then catch them and do it update. This usually faster then some how first existence of row and then do it update.

answered Oct 4, 2014 at 17:44

cvrcek

111 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

Gervs · Accepted Answer · 2014-10-04 18:47:17Z

-1

Create an UNIQUE index for colomn clientguid Then tou can use INSERT .... ON DUPLICATE KEY UPDATE

INSERT INTO yourtable (timestanp, clientguid,clientip, serverip)
VALUES(NOW(), $clientguid, '$clientip', '$serverip')
ON DUPLICATE KEY UPDATE
    timestamp = NOW(),
    clientip = VALUES(clientip),
    serverip = VALUES(serverip)

answered Oct 4, 2014 at 18:47

Gervs

1,3979 silver badges8 bronze badges

1 Comment

CL. Over a year ago

This does not work in SQLite.

Collectives™ on Stack Overflow

SQLite insert if not exists else update [duplicate]

2 Answers 2

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Linked

Related