UPDATE with JOIN error

Databases
1

Finianly I have do ask you for help.
I have to update some records from tableA with data from tableB.
For that I use a form that I got from stackoverflow for sqlite,
but I’m not sure that RealStudio Database accept this form:

DB_Inst.SQLExecute("UPDATE TableA " + _
"SET TableA.ExtraKursBez = TableB.KursBez, " + _
" TableA.ExtraLehrer = TableB.Lehrer " + _
"FROM TableB JOIN TableA " + _
"WHERE TableB.Name = TableA.Name AND " + _
" TableB.DatumC = TableA.DatumC ")

However, I get a syntax error near “.”

2

RealSQLDatabase uses SQLite. You can also use the SQLiteDatabase class in newer versions.

SQLite itself does not support JOINs on UPDATE statements, so you’ll need to write that statement differently. Perhaps SELECTing the rows you first want then then doing a simpler UPDATE in a loop. A correlated subquery might also work.

3

I followed your idea. Now I determine the needed records by a SELECT-Statement and UPDATE them record by record. And, Who’d have thunk, for about 3000 records it only needs about 2 seconds. That’s realy fast.
Thank you, Paul, for your hint.

4

I know that this is doable in Access .mdb files and VB6 but was told it was not possible in SQLite. I made many attempts at it and never could get it to succeed, so in several places I have loops as well. If you have a lot of records, make sure you use transactions (Begin Transaction to start and Commit to write it out to the DB), that really helped the performance in these long loops.

5

Its a shame that SQLite doesnt support update from join.

but it might be possible to do what you want with sub-query

UPDATE TableA SET TableA.ExtraKursBez = (SELECT TableB.KursBez FROM TableB WHERE TableB.Name = TableA.Name AND TableB.DatumC = TableA.DatumC), TableA.ExtraLehrer = (SELECT TableB.LehrerZ FROM TableB WHERE TableB.Name = TableA.Name AND TableB.DatumC = TableA.DatumC)

6

Well, the OP’s problem is selecting the records to be updated. The usual trick is:

Update TableA set someField=someValue where TableA.ID IN (Select TableA.ID from …some complicated query involving lots of joins and what not)

This is also how you delete records.

7

Thank you, Rich and Maximilian, for your suggestions.

In the first code it needs some little correction:

UPDATE TableA SET ExtraKursBez = (SELECT TableB.KursBez FROM TableB JOIN TableA WHERE TableB.Name = TableA.Name AND TableB.DatumC = TableA.DatumC), TableA.ExtraLehrer = (SELECT TableB.LehrerZ FROM TableB JOIN TableA WHERE TableB.Name = TableA.Name AND TableB.DatumC = TableA.DatumC)

The certain problem in the second code is that I have do update TableA with values from TableB and the UPDATE-Statement in SQLite seems not to accept a second table. In:

Update TableA set someField=someValue where ...

I have the problem that I first have to determine “someValue” which is a value from TableB.
Maximilian, I remeber you gave me the tip with " WHERE someField IN (SELECT …" five years ago in the german Real Studio nug. But in that case the value for updaiting was already known (a “x”-literal).

Thank you for your ideas.