Updating data in current DB from backup: cross-DB subquery returns > 1 value

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开发者_如何学JAVA 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?

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:

where ffd.FormFieldDataID = CurrentDB.dbo.FormFieldData.FormFieldDataID