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

Kuberchaun · Accepted Answer · 2009-11-03 18:13:48Z

11

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;

edited Nov 3, 2009 at 18:13

answered Nov 3, 2009 at 17:19

Kuberchaun

30.5k8 gold badges54 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

GrumpyCanuck Over a year ago

I had to change leagues.league_id to league_id, but it worked perfectly! Thanks so much

chiggsy · Accepted Answer · 2009-11-03 17:59:50Z

1

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:

answered Nov 3, 2009 at 17:59

chiggsy

8,6235 gold badges36 silver badges44 bronze badges

Collectives™ on Stack Overflow

Updating records in Postgres using nested sub-selects

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related