使用嵌套子选择更新 Postgres 中的记录
我有一个表，在其中添加了一个新列，我想编写一条 SQL 语句来根据现有信息更新该列。这是两个表和相关列
'leagues' => id => league_key => league_id (this is the new column)
'permissions' => id => league_key
现在，我想要做的，用简单的英语来说，就是
将 leagues.league_id 设置为 Permissions.league_key 的每个值的 requests.id
我尝试过 SQL像这样：
更新联赛 设置联赛 ID = （从权限中选择 ID，其中 league_key = （从联赛中选择不同的（league_key））） WHERE league_key = (SELECT unique(league_key) FROM leagues)
但我收到一条错误消息，显示
错误：用作表达式的子查询返回的不止一行
任何对此的帮助将不胜感激
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
根据您的要求，
将 leagues.league_id 设置为permissions.id，对于permissions.league_key 的每个值，
这样做。
Based on your requirements of
Set leagues.league_id to be permissions.id for each value of permissions.league_key
This does that.
当您将子查询作为表达式执行时，它无法返回结果集。您的子查询必须计算出单个结果。您看到的错误是因为您的子查询之一返回多个值。
这是 pg84 的相关文档：
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: