Update statement referencing another database from same server

Question

Post reply

Update statement referencing another database from same server

dba321

Hall of Fame

Points: 3917
More actions
August 15, 2006 at 3:57 pm

#113267

I'm trying to update a table in MyDB1 with a value in MyDB2 on the same SQL server (2000)
     UPDATE MyDB1.dbo.Users
     SET MyDB1.dbo.Users.InstantASP_UserID = MyDB2.dbo.InstantASP_Users.UserID
     FROM MyDB2
     INNER JOIN dbo.aspnet_Membership a ON a.UserId = MyDB1.dbo.Users.UserID
     WHERE MyDB2.dbo.InstantASP_Users.EmailAddress = a.Email
I don't want to have to create a linked server. It's on the same box.
I'm getting Invalid object name 'MyDB2'.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

FROM MyDB2

The FROM in an UPDATE (or SELECT) expects a table name. You've given it a database name, not a table name.

You don't need a linked server. The databases are all on the same server instance, so no need for a link.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

Obviously, I've not tested this... but it should work...

UPDATE u1

SET InstantASP_UserID = u2.UserID

FROM MyDB1.dbo.Users u1,

MyDB2.dbo.InstantASP_Users u2,

dbo.aspnet_Membership a

WHERE a.UserId = u1.UserID

AND u2.EmailAddress = a.Email

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)