Android Question SQL INSERT OR REPLACE

[mw71]

hi

i test a "new" SQLite statement.

query_gpx=$"INSERT OR REPLACE INTO Berge (id, Berg, Lat, Lon, Cmt, Loc) VALUES (SELECT id FROM Berge WHERE Berg='${SumName}','${SumName}','${lati}','${loni}','${cmt.Replace(loc,"")}','${loc}')"$

The table is Create with this query

query_gpx="CREATE TABLE Berge (id INTEGER PRIMARY KEY AUTOINCREMENT, Berg TEXT, Lat TEXT, Lon TEXT, Cmt TEXT, Loc Text)"

Unfortunly the "Insert or Replace" dont work.
- the Log means: Error is near Select statement
- The select statement (test solo) returns NULL because the data is not in the database

any idears welcome

 

[npsonic]

This might not be the best solution, but you can also add default value for null columns if you are always excpecting some value.

query_gpx="CREATE TABLE Berge (id INTEGER PRIMARY KEY AUTOINCREMENT, Berg Text NOT NULL DEFAULT '' , Lat TEXT NOT NULL DEFAULT '' , Lon TEXT NOT NULL DEFAULT '' , Cmt TEXT NOT NULL DEFAULT '' , Loc TEXT NOT NULL DEFAULT '' )"

 

[mw71]

thanks, but the Problem is at the Select statement in the Insert or Replace.... statement to get the id (Primary Key) from the the Table

 

[RB Smissaert]

hi

i test a "new" SQLite statement.

query_gpx=$"INSERT OR REPLACE INTO Berge (id, Berg, Lat, Lon, Cmt, Loc) VALUES (SELECT id FROM Berge WHERE Berg='${SumName}','${SumName}','${lati}','${loni}','${cmt.Replace(loc,"")}','${loc}')"$

The table is Create with this query

query_gpx="CREATE TABLE Berge (id INTEGER PRIMARY KEY AUTOINCREMENT, Berg TEXT, Lat TEXT, Lon TEXT, Cmt TEXT, Loc Text)"

Unfortunly the "Insert or Replace" dont work.
- the Log means: Error is near Select statement
- The select statement (test solo) returns NULL because the data is not in the database

any idears welcome

I might be wrong, but I don't think you can put a sub-select in a Values clause.
Try without using Values.

RBS

 

[mw71]

i am not sure, i found it e.g. here:
https://tableplus.io/blog/2018/04/sqlite-insert-replace-if-exist.html

 

[RB Smissaert]

i am not sure, i found it e.g. here:
https://tableplus.io/blog/2018/04/sqlite-insert-replace-if-exist.html

Ok, maybe you can. Will try it later.
It looks odd though as there could more rows from the sub-select than the other fields.
Could you post a small sample db to test?

RBS

 

[jimmyF]

You also can't update the id field as it is PRIMARY KEY AUTOINCREMENT.

 

[OliverA]

Looks like you have a missing set of () around the SELECT statement

query_gpx=$"INSERT OR REPLACE INTO Berge (id, Berg, Lat, Lon, Cmt, Loc) VALUES ((SELECT id FROM Berge WHERE Berg='${SumName}'),'${SumName}','${lati}','${loni}','${cmt.Replace(loc,"")}','${loc}')"$

 

[RB Smissaert]

Looks like you have a missing set of () around the SELECT statement

query_gpx=$"INSERT OR REPLACE INTO Berge (id, Berg, Lat, Lon, Cmt, Loc) VALUES ((SELECT id FROM Berge WHERE Berg='${SumName}'),'${SumName}','${lati}','${loni}','${cmt.Replace(loc,"")}','${loc}')"$

Ah, yes, then it works indeed.

RBS

 

[OliverA]

Even though this is an interesting solution, the only reason this is needed is because there are two primary keys, the id field and the Berge field. If the table just had the Berge field as primary key, the sub-select would be unnecessary. I guess were trying to save some space here when setting up relationships? I'm not saying that there will never be cases where a table can have multiple unique columns and you just pick one of them to be the primary key (for example, would not want to use Social Security Number as a key and have it all over the place). But for smaller databases, dual primary keys seem to be a tad overkill.
Oh yeah, not an expert, just an opinion.

 

[mw71]

many thanks for your ansers

Could you post a small sample db to test?

You can use a empty db to test

Looks like you have a missing set of () around the SELECT statement

Yes, but the result the same (i have Test boot)

If the table just had the Berge field as primary key, the sub-select would be unnecessary.

interesting
the data in the Berge field are unique for each dataset

i think (not tested yet and not sure if it possible) the statement to Create the Table is

query_gpx="CREATE TABLE Berge (Berg TEXT PRIMARY KEY, Lat TEXT, Lon TEXT, Cmt TEXT, Loc Text)"

 

[KMatle]

What do you want to achieve? What I understand is:

1. Insert a new mountain (Berg)
2. If there is already a mountain with the same name then update the data

If it is so then

1. Refer to the INPUT (e.g. a user enters the mountain or it comes from another source but it's still from a source outside the table)
2. The query then is easy (forget about the ID. It is set automatically by SQLite by "NULL"):

Sql1.ExecNonQuery2("INSERT OR REPLACE INTO Berge (ID, Berg, Lat, Lon, Cmt, Loc) Values (?,?,?,?,?,?)", Array(Null,BergET, LatET, LonET, CmtET, LocET ))

SQlite checks if the PRIMARY or UNIQUE key exists. If so, the row will be updated. Just give it a try. I didn't test the realtion between the PRIMARY and the UNIQUE key (exactly: Does SQlite ignore the ID if it's not given and relates to the UNIQUE key as wanted).

 

[RB Smissaert]

many thanks for your ansers




You can use a empty db to test

Yes, have tested and with the brackets the sub-select within the Values set does indeed work fine.
If the sub-select in the insert SQL produces more than one row it is still OK and only one row will be inserted.

RBS






Yes, but the result the same (i have Test boot)





interesting
the data in the Berge field are unique for each dataset

i think (not tested yet and not sure if it possible) the statement to Create the Table is

query_gpx="CREATE TABLE Berge (Berg TEXT PRIMARY KEY, Lat TEXT, Lon TEXT, Cmt TEXT, Loc Text)"

 

[mw71]

What do you want to achieve? What I understand is:

1. Insert a new mountain (Berg)
2. If there is already a mountain with the same name then update the data

your right


first test, change the Table Create (thanks JimmyF!)

query_gpx="CREATE TABLE Berge (id INTEGER PRIMARY KEY, Berg TEXT, Lat TEXT, Lon TEXT, Cmt TEXT, Loc Text)"

now it Works (and faster as my old solution ):

query_gpx=$"INSERT OR REPLACE INTO Berge (id, Berg, Lat, Lon, Cmt, Loc) VALUES ((SELECT id FROM Berge WHERE PPER(Berg)='${SumName.ToUpperCase}'),?,?,?,?,?)"$
sql_g_in.AddNonQueryToBatch(query_gpx, Array(SumName,lati,loni,cmt.Replace(loc,""),loc))

 

[Jeffrey Cameron]

Just a thought: That statement is great as long as you're updating every column every time.
If my memory serves (and I'm the first to admit that I'm old ) unlike an UPDATE statement, the REPLACE statement will delete the row if the primary key exists prior to updating the data. Any columns that are omitted from the INTO clause will be reset to their default values if they are not supplied.

 

[jimmyF]

Actually that's a very good point .
And that's my reading of the documentation also.

 

[mw71]

the REPLACE statement will delete the row if the primary key exists prior to updating the data.

right, i have it read in the Docu also:
https://www.sqlite.org/lang_conflict.html

My first way, i check with dbutils.ExecuteMap if the Dataset exist. If True, i Update, else i Insert.
The "Problem" was, the Progressview frozen while this check. Now it Works fine.