SQLite UPSERT with 2 constraints [duplicate]

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
– kaharas, Commented Feb 27, 2013 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
– John Woo, Commented Feb 27, 2013 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
– kaharas, Commented Feb 27, 2013 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
– kaharas, Commented Feb 27, 2013 at 16:03

mvp · Accepted Answer · 2013-02-28 07:20:27Z

4

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).

answered Feb 28, 2013 at 7:20

mvp

118k15 gold badges132 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rajneesh071 · Accepted Answer · 2013-02-27 16:57:47Z

0

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.

edited Feb 27, 2013 at 16:57

answered Feb 27, 2013 at 16:45

Rajneesh071

31.1k15 gold badges63 silver badges74 bronze badges

Collectives™ on Stack Overflow

SQLite UPSERT with 2 constraints [duplicate]

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related