Base join error updating current record

[PatrickDemets]

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

[Ratslinger]

Hello,

This is what I noted in your last question - ...best to be aware of the potential problem. The situation is addressed in this post → updating fk of a record gives an error (libre base ver: 5.1.6.2)

I believe this SQL should work for you:

SELECT book.id, book.book_title, book.author_id, a.id, a.name
FROM book
LEFT JOIN (SELECT id, name from author) a ON book.author_id = a.id;

Note that trying to modify id or name from author table will create an error. However, changing the author_id in the book table should work.

[PatrickDemets]

Thanks again Ratslinger! I didn’t realize I was looking at that very same issue you mentioned; I didn’t really grok it, but I got it now.

The full query (with several more columns) feeds a form where the un-updateable columns are hidden, so there’s no danger of an attempt to update the author table. Works like a charm!

[Ratslinger]

Glad all is working.