Updating record with join in SQL

[daveinhull]

Hi rvk,

Oh ok, many thanks will do.......

Dave

[daveinhull]

Hi Lacak,

Ok, sorry for the delay, but I've uploaded the files I think are needed.

If you do get chance to take a look that would be great. I've also included a small Access DB that is referenced in the Connection parameter string.

Many thanks
Dave

[LacaK]

Look at attached zip.

I have changed your UpdateSQL:

Code: [Select]

UPDATE Table1
SET rowName = :rowName, rowTitle=:rowTitle
WHERE ID=:OLD_ID
(plus some minor changes)
And now it works.

Of coure you can update only one table (in your case Table1) in multi-table join.

[daveinhull]

Hi Lacak,

Many thanks, I think I'm getting it. I may have come close to your solution at some point but probably went on the change the wrong thing.

I don't think I ever tried adding:

Code: [Select]

  SQLTransaction1.CommitRetaining;

Anyway, I now get the picture 

Is it possible to update more than one table in the updateSQL, maybe by adding multiple SQL strings? or by changing the string on the fly during the 'afterpost'?

Thanks again for you help....really appreciated.

Dave 

[rvk]

No, you can't update multiple tables in one statement. You could update the second table in the afterpost by creating a new SQLQuery and doing the UPDATE there manually. (or calling ODBCConnection1.ExecuteDirect(); directly)

But, when you need to do this you might want to reconsider the design of your forms.

Maybe it's better to create a single entry-form where you could change the value. In my opinion separate entry-forms are always to be preferred over a DBGrid-edit. The DBGrid-edit is just a rudimentary quick fix for changing data. It's not meant to be used for complex data-entry.

[daveinhull]

Hi rvk,

Thanks, and as expected. I do agree a proper edit form helping the user to perform the edit would be much better.

I think I was just trying to move from MS Access to FreePascal and testing my understanding of how things related.

Anyway thanks to the great support we get on this site, it really helps.

Thanks again
Dave