Apparently, my SQL is rusty. I need help (again!)
Cutting down to bare minimum, I have two tables (# = PK; * = not null; & = FK):
book (#id, *book_title, &author_id) -- note: author_id is nullable author (#id, *name) book FK: book.author_id = author.id
My (minimum) query:
SELECT book.id, book.book_title, book.author_id, author.id, author.name FROM book LEFT OUTER JOIN author ON book.author_id = author.id;
Query returns expected results (i.e., “book” records with authors and without authors), in Datasheet view (or whatever it’s called in LO Base).
However, when I try to update the column (in Datasheet view) “book.author_id”, I get the error message “Error updating the current record”. I can see the author’s name fetched and populated in the Datasheet, but when I move to another row, I get the error.
AFAIK, if the PK of the referenced table and the FK of the referencing table (i.e., the “link”) are both in the query, then it should be updateable/editable.
What am I doing wrong? Thx