How to make the connection between PK and FK in SQL?

Question

I need to make the connection with the PK from a table to its correspondent FK from another table. This is my db:

I only have the case_id from the case table, and I need the case_uid from the case_test table for this SQL statement:

UPDATE case_test (this part is ok)
SET country = 'Canada' (this part is ok)
WHERE case_test.case_uid = case.case_uid  (is the same as case_uid from the case table but i only know the case_id field from that table)

How can I make the connection between the keys knowing that I only know case_id?

Answer 1

Use a scalar subquery to extract case_uid from table case.

update case_test
set country = 'Canada'
where case_uid = (select case_uid from "case" where case_id = ?);

Btw. could it be that there is more than one case_uid for a case_id in table case? If so then the subquery is not scalar anymore and the where clause shall use in operator instead of =

where case_uid in (select case_uid from "case" where case_id = ?)

Unrelated but case is not a proper name for a table.