sqlite3 if exists alternative

Question

I am trying to run simple query for sqlite which is update a record if not exists.

I could have used Insert or replace but article_tags table doesn't have any primary key as it is a relational table.

How can I write this query for sqlite as if not exists is not supported. And I don't have idea how to use CASE for this ?

Table Structure:

articles(id, content)
article_tags(article_id, tag_id)
tag(id, name)

SQLITE Incorrect Syntax Tried

insert into article_tags (article_id, tag_id ) values ( 2,7)
if not exists  (select 1 from article_tags where article_id =2 AND tag_id=7)

Why not make a compound unique index and use a plain `insert`? Then when it already exists, you'll get an error, which you can catch. — icktoofay, May 18 '13 at 04:45
Can't you create a multi-column primary key, based on those 2 columns? — bart, May 18 '13 at 04:46
OK, I verified, although [SQLite supports multi-columns primary key](http://stackoverflow.com/questions/734689/sqlite-primary-key-on-multiple-columns), it [does not support alter table](http://stackoverflow.com/a/946119/19966) to add it. — bart, May 18 '13 at 04:55

paxdiablo · Accepted Answer · 2013-05-18T04:57:59.377

2

I think the correct way to do this would be to add a primary key to the article_tags table, a composite one crossing both columns. That's the normal way to do many-to-many relationship tables.

In other words (pseudo-DDL):

create table article_tags (
    article_id  int references articles(id),
    tag_id      int references tag(id),
    primary key (article_id, tag_id)
);

That way, insertion of a duplicate pair would fail rather than having to resort to if not exists.

edited May 18 '13 at 04:57

answered May 18 '13 at 04:45

paxdiablo

854,327
234
1,573
1,953

After creating this table, "insert or replace into article_tags" works like it should.Thanks – django May 18 '13 at 05:28

mogul · Answer 2 · 2013-05-18T05:46:46.917

1

This seems to work, although I will, as others have also said, suggest that you add some constraints on the table and then simply "try" to insert.

insert into article_tags 
  select 2, 7 
  where  not exists ( select * 
                      from   article_tags 
                      where  article_id = 2 
                        and  tag_id     = 7 )

edited May 18 '13 at 05:46

answered May 18 '13 at 05:09

mogul

4,441
1
18
22

ahh, sorry. I have edited the answer, a little. seems that sqlite3 accepts to have selects even without specifying a table. Even simpler now. – mogul May 18 '13 at 05:48

sqlite3 if exists alternative

2 Answers2