Android Question SQLite syntax request error

[Lakhtin_V]

I am successfully using SQLite in my application. Requests are processed and tables are updated. I wanted to make a "filter" that would save me from duplicating records.
1. I created a checksum field - CRC.
2. Made this field CRC as primary key.
I specified a condition in the request if we are trying to add a line with duplicate information, then the record will not be added, but it does not work for me, what is my mistake. I tried different spellings of the CPC field with and without quotes with the table name but it does not help.

android.database.sqlite.SQLiteException: near "ON": syntax error (code 1): , while compiling:
INSERT INTO `Target` (`DTime`, `STime`, `CRC`) VALUES (1681747764109, 1681747759803, 'F0F44') ON CONFLICT(`CRC`) IGNORE

 

[coldtech]

I believe you need to define the column as UNIQUE not just as a Primary Key.

 

[Lakhtin_V]

I believe you need to define the column as UNIQUE not just as a Primary Key.

I've tried giving the field different combinations of a UNIQUE and Primary key, all of which doesn't help.

 

[LucaMs]

I think that you don't have to specify the field, CRC, just because it is the primary key of the table.

ON CONFLICT(`CRC`) IGNORE

just:
ON CONFLICT IGNORE


Also, using SQL's ExecuteNonQuery2 you don't have to worry about quotes, double quotes, ...

 

[Lakhtin_V]

I think that you don't have to specify the field, CRC, just because it is the primary key of the table.

just:
ON CONFLICT IGNORE


Also, using SQL's ExecuteNonQuery2 you don't have to worry about quotes, double quotes, ...

There is no problem in quotes, I checked it in the absence of a primary key, but with a primary key, any combinations with ON CONFLICT IGNORE or ON CONFLICT DO NOTHING do not work

 

[Mahares]

but with a primary key, any combinations with ON CONFLICT IGNORE or ON CONFLICT DO NOTHING do not work

If your table has CRC as PRIMARY KEY and you want to avoid duplicate records if CRC is duplicated, you can do:
INSERT OR IGNORE INTO Target.....

 

[LucaMs]

I'm lazy.

https://www.perplexity.ai/search/f778e0d7-b8a5-423d-a3ea-7ed528c3ddab?s=u

 

[LucaMs]

I'm lazy.

https://www.perplexity.ai/search/f778e0d7-b8a5-423d-a3ea-7ed528c3ddab?s=u

After trying pretty hard I have to say that Perplexity is wrong

 

[Lakhtin_V]

If your table has CRC as PRIMARY KEY and you want to avoid duplicate records if CRC is duplicated, you can do:
INSERT OR IGNORE INTO Target.....

INSERT OR IGNORE INTO Target..... ON CONFLICT DO NOTHING ?

 

[LucaMs]

INSERT OR IGNORE INTO Target..... ON CONFLICT DO NOTHING ?

Yes.

 

[LucaMs]

After trying pretty hard I have to say that Perplexity is wrong

So I asket it: "Are you sure? In SQLite..."

and...

but I see no difference

 

[LucaMs]

So I asket it: "Are you sure? In SQLite..."

and...

View attachment 141285

but I see no difference

Query = $"INSERT INTO Category (Id, CategoryName, Description) VALUES (1, "NewCat", "John") ON CONFLICT (Id) DO UPDATE SET CategoryName = "Other";"$

(SQLException) java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near "ON": syntax error)

Nothing to do, it doesn't work. That's enough, because @Mahares fixed it the best way.

 

[Lakhtin_V]

If your table has CRC as PRIMARY KEY and you want to avoid duplicate records if CRC is duplicated, you can do:
INSERT OR IGNORE INTO Target.....

Good advice Mahares, you solved my problem, I did not find such a syntax option in the documentation. Where did you get your magical knowledge from? Thank you very much! INSERT OR IGNORE INTO Target.... only ;-)

 

[LucaMs]

https://www.sqlite.org/lang_insert.html

 

[LucaMs]

https://www.sqlitetutorial.net/sqlite-insert/