Menu
I have to write a query in sqlite to update the record if it exists or insert it if the record do not already exists. I have looked the syntax of INSERT OR REPLACE INTO from here. But in my case, when I execute the query below many times the record is duplicated. i.e If I execute the query 5 times the record is inserted 5 times.
INSERT OR REPLACE INTO NICKS
(id_nick,name_nick,date_creation)
VALUES
('nabeelarif', 'Muhammad Nabeel','2012-03-04')
Have you any idea what I am doing wrong. I am working on android platform and using 'Firefox Sqlite Manager' to test my query.
553
You can use the INSERT OR REPLACE statement to write new rows or replace existing rows in the table. The syntax and behavior of the INSERT OR REPLACE statement is similar to the INSERT statement. Unlike the INSERT statement, the INSERT OR REPLACE statement does not generate an error if a row already exists.
The SQLite REPLACE() function is a string function that allows you to replace all occurrences of a specified string with another string. In this syntax: string is the string that you want to perform the replacement. pattern is the substring to be found in the original string.
UPSERT in SQLite follows the syntax established by PostgreSQL. UPSERT syntax was added to SQLite with version 3.24. 0 (2018-06-04). An UPSERT is an ordinary INSERT statement that is followed by the special ON CONFLICT clause shown above.
Do you have a primary key or unique index defined for your table? I believe the attributes that make up the primary key or a unique index are used to determine if a row already exists or not.
156
You need to have a unique index on one or a combination of those columns.
CREATE UNIQUE INDEX idx_something ON NICKS (id_nick, name_nick, date_creation);
37
In Android, you should use SQLiteDatabase.replace(), which does insert or update. With the Android SQLite implementation, you normally don't use raw queries represented as strings. See http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#replace%28java.lang.String,%20java.lang.String,%20android.content.ContentValues%29
6
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
