Berlin Berlin - 1 month ago 34
SQL Question

SQLite CASE statement - INSERT IF NOT EXISTS

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!

Answer

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.