I have two tables linked by a common field. I can create a query with data from both tables and selected according to a simple criterion.
Is it now possible to edit the data in that query and for the edits to be “passed back” to the initial tables?
Would you give us the query code please?
Using hslqdb, mysql, Oracle, DB2, H2, or another “proper” db management system as the database source backend, then yes, the exception being dBase tables unless you access them via ODBC.
If you are using a spreadsheet, text table, csv file as datasource then no, multitable editing is not possible. You might possibly be able to update multipe referenced tables in hsqldb (embdedded default for ODB files) via direct importation of text tables and appropriate low level SQL commands, rather than the GUI. See the hsqldb user manual for this under “Text Tables”. Such a setup is not the usual default provided by LO.
In general, one uses a form to enter data. If your tables meet the requirements of multitable joins as indicated above, then it is possible to edit data via a form (and corresponding subform) that will be written to the two linked tables.
Alternatively, use the Tools > SQL command to enter your UPDATE commands.
Half a solution, if both indices for the tables are included in the query then the “table” can be edited and data changed. However when I try SQL it fails.
The query is
SELECT “members”.“memnum”, “members”.“Renewalyear”, “payments”.“ID”, “payments”.“Daterenewed”, “payments”.“rep” FROM “payments”, “members” WHERE “payments”.“MemberNo” = “members”.“memnum” AND “payments”.“rep” = ‘N’
but when I enter the following into the SQL dialogue
UPDATE “forupdate”
SET “Renewalyear” = 2019
I get
1: Table not found in statement [UPDATE “forupdate”]
You can not execute UPDATE statements in the SQL query designer, you need to enter that command via Tools > SQL