从备份更新当前数据库中的数据：跨数据库子查询返回> 1 个值
我的服务器上有两个数据库：当前数据库（我们称之为 CurrentDB），以及从 CurrentDB 备份恢复的另一个数据库（我们称之为 备份数据库）。
特定表中有一个特定的 text 列，对于某些行，我需要通过将它们连接在一起来与旧版本的行中的数据合并。
为了确保我提出的子查询有效，我在 Begin/Rollback 区域中运行了它：（
update CurrentDB.dbo.FormFieldData
Set [Text] = (
select ffd.[Text]
from BackupDB.dbo.FormFieldData as ffd
where ffd.FormFieldDataID = FormFieldDataID
)
where FormFieldDataID in (
select ffd.FormFieldDataID
from BackupDB.dbo.FormFieldData as ffd
join BackupDB.dbo.FormFields as ff on ffd.FormFieldID = ff.FormFieldID
join BackupDB.dbo.FormData as fd on ffd.FormDataID = fd.FormDataID
where ff.FormID = 1
and ffd.FormFieldID = 2
and fd.UserID = 3
and Text like '%john smith%'
)
在 FormFieldData 的两个版本中表，FormFieldDataID 是主键。）
不幸的是，运行此命令会导致：子查询返回超过 1 个值。当子查询跟随 =、!=、<、<=、>、>= 或子查询用作表达式时，这是不允许的。
我确信有一些简单的事情我'我丢失了，但我不明白为什么第一个子查询可以返回多个值。
我缺少什么？
I have two databases on my server: the current database (let's call it CurrentDB), and another database which was restored from a backup of CurrentDB (let's call it BackupDB).
There's a specific text column in a specific table that, for some rows, I need to merge with the data from the older version of the row by concatenating them together.
Just to make sure the subqueries I came up with work, I ran this in a Begin/Rollback region:
update CurrentDB.dbo.FormFieldData
Set [Text] = (
select ffd.[Text]
from BackupDB.dbo.FormFieldData as ffd
where ffd.FormFieldDataID = FormFieldDataID
)
where FormFieldDataID in (
select ffd.FormFieldDataID
from BackupDB.dbo.FormFieldData as ffd
join BackupDB.dbo.FormFields as ff on ffd.FormFieldID = ff.FormFieldID
join BackupDB.dbo.FormData as fd on ffd.FormDataID = fd.FormDataID
where ff.FormID = 1
and ffd.FormFieldID = 2
and fd.UserID = 3
and Text like '%john smith%'
)
(In both versions of the FormFieldData table, FormFieldDataID is the primary key.)
Unfortunately, running this causes: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I'm sure there's something simple that I'm missing, but I don't understand why the first subquery can ever return more than one value.
What am I missing?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
在子查询中，列名称默认为内表，因此您实际上是在计算 1=1，从而返回所有行。尝试创建如下谓词：
In subqueries the column names default to the inner table, so you're essentially evaluating 1=1, thus returning all rows. Try making a predicate like: