Update join with Tquery UpdateSql

[lazzaro2023]

I have two tables: turno and richieste.
In the TQuery component, I have the following SQL code:
In SQL.Text:

SELECT
t.settimana,
t.anno,
t.numero_fascicoli,
t.con_assistente,
t.start_sede,
t.end_sede,
t.ref_operatore,
t.ref_assistente,
r.*
FROM turno t
LEFT JOIN richieste r ON t.ref_richieste = r.id

And in UpdateSQL.Text:

UPDATE turno t
LEFT JOIN richieste r ON t.ref_richieste = r.id 
SET
t.settimana = :settimana, 
t.anno = :anno,
t.numero_fascicoli = :numero_fascicoli,
t.con_assistente = :con_assistente,
t.start_sede = :start_sede,
t.end_sede = :end_sede,
t.ref_operatore = :ref_operatore,
t.ref_assistente = :ref_assistente,
r.ref_richiedente = :ref_richiedente, 
r.data_start = :data_start
WHERE t.id = :id

I have a DBGrid on the form linked to the TQuery as a data source. But when I update a value in the DBGrid and press post on the DBNavigator, Lazarus gives a database error specifically "DATABASE: near "t": sytax error."

can anyone help me?

[rvk]

You don't mention the database you use but...

Normally you can't update multiple tables in one statement.

Depending on the database you could use a join structure but the actual "update table set" needs to specify only 1 table.

You might get away with this:

Code: SQL  [Select][+][-]

1. UPDATE turno t, richieste r
2. SET
3.  t.settimana = :settimana,
4. <etc>
5. WHERE t.ref_richieste = r.id

But again... it depends on the database.

[lazzaro2023]

I use Sqlite

[rvk]

Multi table updates are not supported by SQLite.

You either need to do 2 updates or create a view with update-trigger and update the view.
Like shown here: https://stackoverflow.com/a/68191776/1037511

[lazzaro2023]

I have this scenario:
On the form, there is a query containing SQL code as follows:

SELECT
  t.id as TurnoID,
  t.settimana,
  t.anno,
  t.numero_fascicoli,
  t.con_assistente,
  t.start_sede,
  t.end_sede,
  t.ref_operatore,
  t.ref_assistente,
  r.*
FROM turno t
left JOIN richieste r ON t.ref_richieste = r.id

Dbgrid1 correctly displays the query results, but when I want to perform an update, I find myself needing to use two separate queries to update tables involved in the join.
In the Tquery's UpdateSql, what SQL code should I write?

[rvk]

You can't use two updates in Tquery's UpdateSql.

You could update the second table in the afterpost by creating a new SQLQuery and doing the UPDATE there manually. (or calling SQLConnection1.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.

[lazzaro2023]

You can't use two updates in Tquery's UpdateSql.

You could update the second table in the afterpost by creating a new SQLQuery and doing the UPDATE there manually. (or calling SQLConnection1.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.

If I have comprehended the process accurately:
Within the context of the updateSQL operation, I am effecting updates across all fields within the "turno" table. Subsequently, in the "afterpost" phase, I proceed to perform updates on the "richieste" table via a distinct and separate query.

[rvk]

If I have comprehended the process accurately:
Within the context of the updateSQL operation, I am effecting updates across all fields within the "turno" table. Subsequently, in the "afterpost" phase, I proceed to perform updates on the "richieste" table via a distinct and separate query.

The TSQLQuery has an AfterPost event.
https://lazarus-ccr.sourceforge.io/docs/fcl/db/tdataset.afterpost.html

So after you change multiple values in the grid there is a call to TSQLQuery.Post (automatically or via button). You can only have one update sql in updatesql which will run. You can update the other fields (for the other table) in the TSQLQuery.AfterPost event.

But as I already stated... this is bad design and kind of a hack.

[Zvoni]

Or just don't use the Insert/Update/DeleteSQL-Properties of TSQLQuery (as rvk mentioned with ExecuteDirect).
No idea if/how it's implemented in TSQLQuery but different DBMS have different Syntaxes for UPDATES with Joins.

That said, i don't understand your problem.
I'd rather use OnEditingDone.
1) StartTransaction
2) in OnEditingDone
a) Grab the PrimaryKey if it's a field of table r/ForeignKey if it's from table t  -Value depending on the Line of your Grid (easy with hidden columns)
b) Execute an UPDATE for each Field separately
e.g. UPDATE t SET anno=:anno WHERE ref_richieste =:ID
3) Commit Transaction

Would even work across multiple lines

[lazzaro2023]

Or just don't use the Insert/Update/DeleteSQL-Properties of TSQLQuery (as rvk mentioned with ExecuteDirect).
No idea if/how it's implemented in TSQLQuery but different DBMS have different Syntaxes for UPDATES with Joins.

That said, i don't understand your problem.
I'd rather use OnEditingDone.
1) StartTransaction
2) in OnEditingDone
a) Grab the PrimaryKey if it's a field of table r/ForeignKey if it's from table t  -Value depending on the Line of your Grid (easy with hidden columns)
b) Execute an UPDATE for each Field separately
e.g. UPDATE t SET anno=:anno WHERE ref_richieste =:ID
3) Commit Transaction

Would even work across multiple lines

Thank you!
Very interesting as a solution.
I appreciate the time you've dedicated to me.