SQLite insert if not exists else update

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 http://stackoverflow.com/questions/418898/sqlite-upsert-not-insert-or-replace ......................... — edelwater, Oct 04 '14 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, Oct 04 '14 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, Oct 04 '14 at 15:32
@dotNNJ what is your Unique/PK? Is $ClientGUID unique or not over your rows? Ergo: do you want to do massive updates or just only 1 ? — edelwater, Oct 04 '14 at 15:52
Only one at a time. There can be duplicate serverip, clientip, and timestamp values, but never two of the same clientguid — dotNNJ, Oct 04 '14 at 16:31

score 0 · Answer 1 · answered Oct 04 '14 at 17:44

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 04 '14 at 17:44

cvrcek

11
1

score -1 · Answer 2 · answered Oct 04 '14 at 18:47

-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 04 '14 at 18:47

Gervs

1,397
9
8

This does not work in SQLite. – CL. Oct 04 '14 at 20:28

SQLite insert if not exists else update

2 Answers2