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?
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, ?);
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.
Take the approach described here, giving the table a primary key, and using the
INSERT OR IGNORE syntax, always using the same key value.