SQLite UPSERT with 2 constraints

Question

I have an sql table with 3 columns, none of which is UNIQUE ( but the pair name + role is ):

 Name | Role | Votes

What I need to do is, write an sqllite query that stick to the following rules :

If a row with the given name and role already exist, votes is incremented by 1
If not, a new row is created with Votes = 1

I've looked into INSERT OR REPLACE and this great post but it doesn't seem to help me that much, and I'm not even sure INSERT OR REPLACE is a viable option, since something like

INSERT OR REPLACE INTO words (name,role,votes) 
VALUES ('foo','bar', coalesce((
    select votes from words where name = 'foo' and role='bar'),0)+1)

always insterts and never replace

It's not a duplicate, since none of my columns is unique or can be set as such — kaharas, Feb 27 '13 at 15:06
you only need to add compound unique column `CONSTRAINT tb_UQ UNIQUE (Name, RoleID)` and the link i gave you will work. — John Woo, Feb 27 '13 at 15:08
And you are right :) in any case, I think that the query i posted, once added the constraint to the table, is better than the one you linked, isn't it? — kaharas, Feb 27 '13 at 15:31
What do you mean by get the counter ? you want to know what's that table for, or the reason of the select? — kaharas, Feb 27 '13 at 16:03
to increment by 1 the former value in case there's one ... right, that's votes, not counter, my bad — kaharas, Feb 28 '13 at 08:42

score 4 · Accepted Answer · answered Feb 28 '13 at 07:20

You simply need to create unique index over your 2 columns for this to work:

CREATE UNIQUE INDEX words_name_role_idx ON words (name,role)

Note that you do not create unique index for any single column, but for combination of 2 as a whole.

After that, your REPLACE INTO statement should start working correctly:

REPLACE INTO words (name,role,votes) VALUES ('foo','bar',
    coalesce((
        SELECT votes FROM words
        WHERE name = 'foo' AND role='bar'),0
    )+1
)

(note that I have changed counter to votes above).

Rajneesh071 · Answer 2 · 2013-02-27T16:57:47.087

This query will update your record with +1.

update todo set text='raj',complete='raj',pk=((SELECT pk FROM todo where text='raj' and complete='raj')+1) where (SELECT pk FROM todo where text='raj' and complete='raj')

EDIT YOUE QUERY

update words set name='foo',role='bar', votes =((SELECT votes FROM words where name='foo' and role='bar')+1) where (SELECT votes FROM words where name='foo' and role='bar')

And make insert query if this condition will not true.

SQLite UPSERT with 2 constraints

2 Answers2