android SQLite update/insert

Question

I want to UPDATE a row in my table, WHERE key = LastSelected
If a row with that key does not exist, I want to INSERT it.

I can get the UPDATE to work if the row already exists, but it will not INSERT if it is missing.

I have tried these (the first one correctly updates, but does not insert) :

String.format("UPDATE table_1 SET value = '%s' WHERE key = 'LastSelected'", s); String.format("REPLACE table_1 SET value = '%s' WHERE key = 'LastSelected'", s); String.format("INSERT OR REPLACE INTO table_1 SET value = '%s' WHERE key = 'LastSelected'", s);

You should look in to prepared statements - generally, string interpolation with String.format is an unsafe way to prepare SQL, as it is difficult to make sure everything is properly escaped to make the SQL correct and avoid SQL injections. Prepared statements let you insert placeholders (typically '?') in your SQL, and then bind values to those parameters. Everything is then handled by the SQL engine to make sure that the data is encoded properly. — Michael Ekstrand
– Michael Ekstrand, Commented Dec 31, 2011 at 22:23

Markus Jarderot · Accepted Answer · 2011-08-24 19:30:51Z

The syntax is INSERT OR REPLACE INTO <table> (<columns>) VALUES (<values>), as can be seen in the documentation.

In your case, it would be something like this:

INSERT OR REPLACE INTO table_1 (key, value) VALUES ('LastSelected', '...')

With INSERT OR REPLACE you have to remember to report all values. If it finds some null value for a column it will put the default value.

Collectives™ on Stack Overflow

android SQLite update/insert

1 Answer 1

1 Comment

Hot Network Questions