Insert row into table if it is empty and replace the existing row if it isn't in SQLite

Question

I have a table in SQLite 3:

CREATE TABLE foo
(
bar TEXT
);

It always has 0 or 1 rows. I want to write a query which updates the row if there is 1 row and adds the row if there are 0. What is the cleanest way to do it?

Answer 1

You could do something like

IF (SELECT COUNT(*) FROM foo) = 0  
    BEGIN  
        INSERT INTO foo VALUES ('text')  
    END  
ELSE  
    BEGIN  
        UPDATE foo SET baa = 'text' 
    END

Hope this helps.

Answer 2

Take the approach described here, giving the table a primary key, and using the INSERT OR IGNORE syntax, always using the same key value.

Answer 3

if you could add one more unique field, then you can:

INSERT OR REPLACE INTO foo (id, bar) VALUES (1, ?);

the table creation is in this case:

CREATE TABLE foo (id INTEGER NOT NULL, bar TEXT, UNIQUE (id));

or as Alexey found it out, without the primary key:

INSERT OR REPLACE INTO foo (rowid, bar) VALUES (1, ?);