Update one table with data from another table: either i get "single value expected" or no values are updated

[torpedorombolo]

I’ve imported 1 xlsx file and its 2 sheets as tables into Libreoffice Base. These are: membership and contract. Membership contains a list of membership id-s (not auto incremented and this column is the primary key), workers, status, contract begins, contract ends. There are duplicates among workers, because some of them have multiple contracts and therefore multiple membership id-s. And there is the contract table: it containcs the contract (primary key), worker (again duplicates), status, contract begins, contract ends, membership id. Some of the membership ids are missing, I should update them from the other table.

This is the best I’ve come up with so far:

UPDATE “contract”
SET “contract”.“membership_id” = (select “membership”.“id” FROM “membership” WHERE “worker” = “contract”.“worker”)
WHERE “contract”.“membership_id” IS NULL

When I run this, not a single value is updated. I guess the reason why I keep failing is the presence of duplicates. I have no idea how to find the correct values for membership id. A worker can have multiple contracts, the only way i can tell them apart is the use of contract begins and contract ends columns (both tables contain these info). But again, some values are missing there. I have no idea how to start. Hopefully I was able to explain it well enough.

[nicholas59]

There are two points of view in your presentation: the first (technical) requires a relationship review especially in the primary keys choice.
the last (political) puts me back in the eighteenth century to re-read the famous

Contrat Social

Conclusion: your sentences are “dreadful”. Cannot help you that way.

Please be so kind to reconsider that LO Base is not MS Access.

Regards

[RobertG]

@torpedorombolo : Please upload a little example. So we could better see what you want to get.

[Villeroy]

Does the inner SELECT statement return unique membership.id values?

[Wanderer]

I’d start to check, what your SELECT-statements really give and check

SELECT  "membership"."id" FROM "membership" ORDER BY "worker"

and

SELECT contract.membership_id”, (select membership.id FROM membership WHERE worker = contract.worker)
WHERE contract.membership_id IS NULL

And wgen you write

@torpedorombolo

the only way i can tell them apart is the use of contract begins and contract ends columns (both tables contain these info).

then I’d expect to have this also in your condition as an AND contract.begin=membership.begin or similiar.
.
PS: Base can use several types of databases. As you tagged beginner we will here assume at the bottom of your database window in status bar you find something like “HSQLDB embedded”.