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 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.
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?
set Field2 = Table2.Field2 + Table2.Field3
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
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.
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!!!