M mw71 Well-Known Member Licensed User Longtime User Sep 27, 2018 #1 hi i test a "new" SQLite statement. Code: Copy 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 Code: Copy 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
hi i test a "new" SQLite statement. Code: Copy 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 Code: Copy 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 Active Member Licensed User Sep 27, 2018 #2 This might not be the best solution, but you can also add default value for null columns if you are always excpecting some value. Code: Copy 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 '' )" Upvote 0
This might not be the best solution, but you can also add default value for null columns if you are always excpecting some value. Code: Copy 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 '' )"
M mw71 Well-Known Member Licensed User Longtime User Sep 27, 2018 #3 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 Upvote 0
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
R RB Smissaert Well-Known Member Licensed User Longtime User Sep 27, 2018 #4 mw71 said: hi i test a "new" SQLite statement. Code: Copy 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 Code: Copy 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 Click to expand... I might be wrong, but I don't think you can put a sub-select in a Values clause. Try without using Values. RBS Upvote 0
mw71 said: hi i test a "new" SQLite statement. Code: Copy 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 Code: Copy 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 Click to expand... I might be wrong, but I don't think you can put a sub-select in a Values clause. Try without using Values. RBS
M mw71 Well-Known Member Licensed User Longtime User Sep 27, 2018 #5 i am not sure, i found it e.g. here: https://tableplus.io/blog/2018/04/sqlite-insert-replace-if-exist.html Upvote 0
i am not sure, i found it e.g. here: https://tableplus.io/blog/2018/04/sqlite-insert-replace-if-exist.html
R RB Smissaert Well-Known Member Licensed User Longtime User Sep 27, 2018 #6 mw71 said: i am not sure, i found it e.g. here: https://tableplus.io/blog/2018/04/sqlite-insert-replace-if-exist.html Click to expand... 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 Upvote 0
mw71 said: i am not sure, i found it e.g. here: https://tableplus.io/blog/2018/04/sqlite-insert-replace-if-exist.html Click to expand... 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 Active Member Licensed User Longtime User Sep 27, 2018 #7 You also can't update the id field as it is PRIMARY KEY AUTOINCREMENT. Upvote 0
OliverA Expert Licensed User Longtime User Sep 27, 2018 #8 Looks like you have a missing set of () around the SELECT statement Code: Copy 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}')"$ Upvote 0
Looks like you have a missing set of () around the SELECT statement Code: Copy 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}')"$
R RB Smissaert Well-Known Member Licensed User Longtime User Sep 27, 2018 #9 OliverA said: Looks like you have a missing set of () around the SELECT statement Code: Copy 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}')"$ Click to expand... Ah, yes, then it works indeed. RBS Upvote 0
OliverA said: Looks like you have a missing set of () around the SELECT statement Code: Copy 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}')"$ Click to expand... Ah, yes, then it works indeed. RBS
OliverA Expert Licensed User Longtime User Sep 27, 2018 #10 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. Upvote 0
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.
M mw71 Well-Known Member Licensed User Longtime User Sep 27, 2018 #11 many thanks for your ansers RB Smissaert said: Could you post a small sample db to test? Click to expand... You can use a empty db to test OliverA said: Looks like you have a missing set of () around the SELECT statement Click to expand... 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. Click to expand... 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 Code: Copy query_gpx="CREATE TABLE Berge (Berg TEXT PRIMARY KEY, Lat TEXT, Lon TEXT, Cmt TEXT, Loc Text)" Upvote 0
many thanks for your ansers RB Smissaert said: Could you post a small sample db to test? Click to expand... You can use a empty db to test OliverA said: Looks like you have a missing set of () around the SELECT statement Click to expand... 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. Click to expand... 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 Code: Copy query_gpx="CREATE TABLE Berge (Berg TEXT PRIMARY KEY, Lat TEXT, Lon TEXT, Cmt TEXT, Loc Text)"
KMatle Expert Licensed User Longtime User Sep 27, 2018 #12 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"): Code: Copy 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). Upvote 0
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"): Code: Copy 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).
R RB Smissaert Well-Known Member Licensed User Longtime User Sep 27, 2018 #13 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 Code: Copy query_gpx="CREATE TABLE Berge (Berg TEXT PRIMARY KEY, Lat TEXT, Lon TEXT, Cmt TEXT, Loc Text)" Click to expand... Upvote 0
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 Code: Copy query_gpx="CREATE TABLE Berge (Berg TEXT PRIMARY KEY, Lat TEXT, Lon TEXT, Cmt TEXT, Loc Text)" Click to expand...
M mw71 Well-Known Member Licensed User Longtime User Sep 27, 2018 #14 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 Click to expand... your right first test, change the Table Create (thanks JimmyF!) Code: Copy 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 ): Code: Copy 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)) Upvote 0
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 Click to expand... your right first test, change the Table Create (thanks JimmyF!) Code: Copy 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 ): Code: Copy 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 Well-Known Member Licensed User Longtime User Sep 27, 2018 #15 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. Upvote 0
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 Active Member Licensed User Longtime User Sep 28, 2018 #16 Actually that's a very good point . And that's my reading of the documentation also. Upvote 0
M mw71 Well-Known Member Licensed User Longtime User Sep 28, 2018 #17 Jeffrey Cameron said: the REPLACE statement will delete the row if the primary key exists prior to updating the data. Click to expand... 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. Upvote 0
Jeffrey Cameron said: the REPLACE statement will delete the row if the primary key exists prior to updating the data. Click to expand... 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.
