SQLite JOIN question

Databases
1

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

UPDATE recordset with:
Table1.Field2 = Table2.Field2 + Table2.Field3

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.

Thanks for any insight.

2

you have the primary key of both tables in the recordset ?
I’d have to look in the plugin to see what it does with this set up.

3

Yes, in the JOIN portion of the SQL statement with ON keyword, but not in the SELECT portion of SQL statement, but I think that is what you mean.

4

you can perform update with join statement.

here is example:


update a
set Field2 = b.Field2 + b.Field3

from Table1 a
join Table2 b on b.Field1 = a.Field1
5

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.

6

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?

7

a and b are aliases

instead of writing

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”

8

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.

9

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.

10

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.

11

[quote=136940:@Rich Hatfield]update a
set Field2 = b.Field2 + b.Field3

from Table1 a
join Table2 b on b.Field1 = a.Field1[/quote]
No luck Rich, always gives an error:

[Err] 1 - near “FROM”: syntax error

This works perfectly fine in MS Access, so I think there may be some limitations with SQLite as Norman mentions above.

12

Hey Merv,

I was in MS SQL when I wrote the statement and you are right… looks like Sqlite is not a fan of typical syntax for an update with join.

but… when there a will there is a way :slight_smile:

I treated Field2 and Field3 as string datatypes. if they are int values… change || to +

update Table1 set Field2 = (select Table2.Field2 || Table2.Field2 from Table2 where Table2.Field1 = Table1.Field1)

13

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!!!