Update just one field from backup

Question

I'm looking to restore one field from a backup and can't find the syntax for an update statement that can look at 2 different catalogs.

Seems like it should be something fairly close to:

update users set idUserCompany = 
   (select idUserCompany from .myBackup.dbo.users uT) 
where uT.idUser = idUser

Note: Backup used here in a generic sense. The point is that i have good data in the database named .myBackup and need to pull one file into my production db where a match exists in the idUser field between backup and production.

How's a join look in an update statement?

Answer 1

AFAIK that's not how BACKUP is designed to work, not even close.

What I've had to do is RESTORE to a new database, then do an UPDATE via a JOIN on the relevant key.

Answer 2

Thankx for the pointer to JOIN:

update Users set users.idUserCompany = backup.idUserCompany
 from Users inner join .myBackup.dbo.users as old 
on users.idUser = old.idUser