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?
CodePudding user response:
DISCLAIMER: Use at your own risk! Ensure you have a backup before executing the UPDATE statement below. Check the inner EXISTS SELECT statement before applying the update.
UPDATE
case_test
SET
country = 'Canada'
WHERE
EXISTS (
SELECT
ct2.case_test_id /* EXISTS function returns TRUE if any row is returned, so you can use any column or literal value in SELECT body */
FROM
case_test AS ct2 /* you need to give an alias inside EXISTS function to not conflicts with the UPDATE table name */
INNER JOIN
case AS c2 /* The same here for an alias */
ON c2.case_uid = ct2.case_uid
WHERE
ct2.case_uid = case_test.case_uid /* Here the link between the PK of outer case_test table from UPDATE and case_test (as ct2) inside this EXISTS function */
AND c2.case_id = ? /* Here you set your case_id know value */
)
CodePudding user response:
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.