Search code examples
postgresqlsql-updatecursor

Update table based on current record

In my PostgreSQL database I'm converting from integer keys to UUID for various reasons. So right now I have table Person with an ident serial primary key, and table TeamPerson with a person_id integer references Person (ident).

I then go and add a uid UUID column with a unique constraint to Person and a person_uid references Person (uid) column to TeamPerson. So now I need to make all the person_uid correspond to the person_id references. I'm wanting something like:

UPDATE TeamPerson
SET person_uid = (
    SELECT uid FROM Person WHERE ident = current_team_person_row.person_id
)

The current_team_person_row is my issue I'm trying to solve. I think I need a cursor with a WHERE CURRENT OF but I'm just not grasping how to do this.

Solution

  • You are probably looking for: correlated subquery, known also as "dependent subquery"

    In a SQL database query, a correlated subquery (also known as a synchronized subquery) is a subquery (a query nested inside another query) that uses values from the outer query. 

    UPDATE TeamPerson tp
SET person_uid = (
    SELECT uid FROM Person p 
    WHERE p.ident = tp.person_id
)