Question 90415 in PostgreSQL Question posted in
The official documentation can be found here.

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

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

enter image description here

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?

Tags:

2

Answers


  1. 0

    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 */
    )
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search