Android Question SQL INSERT OR REPLACE

mw71 · Sep 27, 2018

hi

i test a "new" SQLite statement.

B4X:

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

B4X:

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 · Sep 27, 2018

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

B4X:

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 · Sep 27, 2018

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 · Sep 27, 2018

mw71 said:
hi

i test a "new" SQLite statement.

B4X:

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

B4X:

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 · Sep 27, 2018

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

RB Smissaert · Sep 27, 2018

mw71 said:
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 · Sep 27, 2018

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

OliverA · Sep 27, 2018

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

B4X:

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 · Sep 27, 2018

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

B4X:

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 · Sep 27, 2018

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 · Sep 27, 2018

many thanks for your ansers

RB Smissaert said:
Could you post a small sample db to test?

You can use a empty db to test

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

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

OliverA said:
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

B4X:

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

KMatle · Sep 27, 2018

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"):

B4X:

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 · Sep 27, 2018

mw71 said:
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

B4X:

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

mw71 · Sep 27, 2018

KMatle said:
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!)

B4X:

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 ):

B4X:

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 · Sep 27, 2018

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 · Sep 28, 2018

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

mw71 · Sep 28, 2018

Jeffrey Cameron said:
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.

Android Question SQL INSERT OR REPLACE

mw71

Active Member

npsonic

Active Member

mw71

Active Member

RB Smissaert

Well-Known Member

mw71

Active Member

RB Smissaert

Well-Known Member

jimmyF

Active Member

OliverA

Expert

RB Smissaert

Well-Known Member

OliverA

Expert

mw71

Active Member

KMatle

Expert

RB Smissaert

Well-Known Member

mw71

Active Member

Jeffrey Cameron

Well-Known Member

jimmyF

Active Member

mw71

Active Member

Similar Threads

Android Question SQL INSERT OR REPLACE

Active Member

Active Member

Active Member

Well-Known Member

Active Member

Well-Known Member

Active Member

Expert

Well-Known Member

Expert

Active Member

Expert

Well-Known Member

Active Member

Well-Known Member

Active Member

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency