Updating records in Postgres using nested sub-selects

Question

I have a table where I have added a new column, and I want to write a SQL statement to update that column based on existing information. Here are the two tables and the relevant columns

'leagues'
=> id
=> league_key
=> league_id (this is the new column)

'permissions'
=> id
=> league_key

Now, what I want to do, in plain English, is this

Set leagues.league_id to be permissions.id for each value of permissions.league_key

I had tried SQL like this:

UPDATE leagues SET league_id = (SELECT id FROM permissions WHERE league_key = (SELECT distinct(league_key) FROM leagues)) WHERE league_key = (SELECT distinct(league_key) FROM leagues)

but I am getting an error message that says

ERROR: more than one row returned by a subquery used as an expression

Any help for this would be greatly appreciated

Answer 1

Based on your requirements of

Set leagues.league_id to be permissions.id for each value of permissions.league_key

This does that.

UPDATE leagues
SET league_id = permissions_id
FROM permissions
WHERE permissions.league_key = leagues.league_key;

Answer 2

When you do a subquery as an expression, it can't return a result set. Your subquery must evaluate to a single result. The error that you are seeing is because one of your subqueries returns more than one value.

Here is the relevant documentation for pg84: