I’ve tried and tried, but from what I can tell from an SQLite book I have, a recordset that is populated from 2 tables that are JOINed is not updatable. Is there any way to do this with SQLite? I know this can be done with VB6 / Access database, because the code works fine in an older product of ours. But Xojo / SQLite is this even possible. Simplified example:
Table1
Field1
Field2
Table2
Field1
Field2
Field3
Table1 JOIN Table2 ON Table1.Field1 = Table2.Field1
Right now, the recordset cannot be updated because it was built from a JOIN, at least that is what I think the documentation is saying. Is this just how it is? Currently, the workaround is to use two recordsets then calculate on one and update the other, but was hoping to do it in one single records or an SQL UPDATE statement in one shot.
Like I said I’d have to peek into the sqlite plugin code to see if it will do anything even if in the select list of columns you grab the primary keys
That’d be the first thing to try
I had a very cursory glance at this and it looks like IF you have the first column returned be from the table you eventually want to update & also select the primary key then it will work.
Hi Rich, what is “a” and “b” in your example? In my example above, I referred to the tables as Table1 and Table2 and the fields as Field1, Field2, etc… Not sure what “a” and “b” represent in your example as it relates to mine?
update Table1
set Field2 = Table2.Field2 + Table2.Field3
from Table1
join Table2 b on Table2.Field1 = Table1.Field1
you make aliases to shorten your code NOTE : there can be other reasons to use them but thats outside the scope of this reply
so instead you write
update a
set Field2 = b.Field2 + b.Field3
from Table1 a
join Table2 b on b.Field1 = a.Field1
a is an alias meaning “Table1”
b is an alias meaning “Table2”
OK, thanks, I am used to the “As” keyword in VB6/Access, but I know what you mean. Let me work with it some more, I can’t seem to get it to work in my Database editor, syntax error somewhere, but I’ll work with it. After I get that to work, I’ll put it in code with the recordset and try it there, which is the real goal.
I am almost positive I read somewhere on the forums that the JOIN makes the resulting Recordset non-updatable, but I could be mistaken.
Views are frequently not updatable.
Single queries that return data from multiple tables are often not updatable as well.
But its not hard & fast and from what I can see in the plugin may just hinge on the table & primary keys - but then I did not go through the code in great detail just skimmed it.
OK, thanks, no need to comment further. I’ll play around with what I have here and if I can get it to work, then great. If not, I have something that works a little slower as is.
Holy cow, you’re Batman, that worked! Can’t tell you how many times I have gone in there and tried this or that and had to fall back to my workaround. That works in the database editor, now let me pound this into the code and see how it is. Thanks for the help, made my evening!!!