SQLite CASE statement - INSERT IF NOT EXISTS

Question

I am using SQLite database.

I want to insert id value or url Value if id Value not exists, but there is no support of IF() function, how should I write it with SQL CASE statement?

require 'sqlite3'
$db.execute("
IF NOT EXISTS(select * from table WHERE id='123456')
BEGIN
INSERT INTO sap(id,url) VALUES('123456', 'https:/test.com');
END;")

Any help would be appreciated,

Thanks!

Also see http://stackoverflow.com/questions/418898/sqlite-upsert-not-insert-or-replace — juergen d, Oct 20 '16 at 14:21

score 1 · Accepted Answer · answered Oct 20 '16 at 14:21

1

One method is to write this as a single statement:

INSERT INTO sap(id, url)
    SELECT id, url 
    FROM (SELECT '123456' as id, 'https:/test.com' as url) t
    WHERE NOT EXISTS (SELECT 1 FROM sap WHERE sap.id = t.id);

However, the correct answer is to use a unique index/constraint so the database does the checking internally:

CREATE UNIQUE INDEX unq_sap_id ON sap(id);

If you attempt to insert a duplicate value for id, the INSERT will fail.

answered Oct 20 '16 at 14:21

Gordon Linoff

1,198,228
53
572
709

thank you, so I just need to create the index one time, right ? – Berlin Oct 20 '16 at 14:27
@Berlin . . . Yes. Once you create it the database will maintain it. – Gordon Linoff Oct 21 '16 at 01:50
thanks again for your help! – Berlin Oct 21 '16 at 14:36

SQLite CASE statement - INSERT IF NOT EXISTS

1 Answers1