使用 JOIN 和内部查询更新表
我导入了一个包含无效状态的成员表。它不包含州缩写，而是包含完整的州名称。
下面的查询确实有效，并提取 Members.StateName 和 State.Abbreviation ：
Connecticut CT
Maryland MD
Massachusetts MA
现在我正在尝试编写和更新命令，其中我将 Members.StateName 替换为 State.Abbreviation 。
UPDATE dbo.Members
SET memState = (SELECT s.stAbv FROM dbo.State AS s
JOIN Members AS m ON s.stName = m.memState )
WHERE memState NOT IN (SELECT s.stAbv FROM dbo.State AS s)
错误：子查询返回超过 1 个值。当子查询跟在 =、!=、<、<=、>、>= 后面或子查询用作表达式时，不允许这样做。
语句已终止。 我知道该错误表示我的内部查询返回多行，因此它无法更新。我该如何让它发挥作用？
I have imported a Members table which has invalid States. Instead of having the State Abbreviation, it contains the completed State Name.
The below query does work and pulls up the Members.StateName and State.Abbreviation :
Connecticut CT
Maryland MD
Massachusetts MA
Now I am trying to write and Update command where I am replacing the Members.StateName with the State.Abbreviation .
UPDATE dbo.Members
SET memState = (SELECT s.stAbv FROM dbo.State AS s
JOIN Members AS m ON s.stName = m.memState )
WHERE memState NOT IN (SELECT s.stAbv FROM dbo.State AS s)
ERROR: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
I understand the error says my inside query returns multiple rows, so it can not Update. How do I get this to work?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我认为你可以这样做：
不完全确定你的 WHERE 子句，但很可能不需要它。
I think you could do it like this:
Not entirely sure about your WHERE clause, but most probably it's not needed.
您不能为 memState 分配超过 1 个值。尝试使用 SELECT DISTINCT 将重复值连接到一个值。
You can not assign more than 1 value to memState. Try SELECT DISTINCT to join duplicate values to one value.