Ask Your Question

edit data in a Base query [closed]

asked 2014-06-09 13:54:05 +0100

allanoptical gravatar image

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?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-25 00:16:21.634315


Would you give us the query code please?

frofa gravatar imagefrofa ( 2014-06-09 14:01:35 +0100 )edit

2 Answers

Sort by » oldest newest most voted

answered 2014-06-09 14:45:50 +0100

Alex Thurgood gravatar image

updated 2014-06-09 14:47:31 +0100

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.

edit flag offensive delete link more

answered 2014-06-09 18:09:05 +0100

allanoptical gravatar image

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"]

edit flag offensive delete link more


You can not execute UPDATE statements in the SQL query designer, you need to enter that command via Tools > SQL

Alex Thurgood gravatar imageAlex Thurgood ( 2014-06-28 22:39:49 +0100 )edit

Question Tools

1 follower


Asked: 2014-06-09 13:54:05 +0100

Seen: 1,953 times

Last updated: Jun 09 '14