I'm trying to update a table in one database with data from a table in
another database on the same server, but I'm having trouble with my naming
convention.

Here's the script:

update DB1.dbo.table1
set col1 = Db2.dbo.table1.col1
from DB1.dbo.table1
inner join
DB2.dbo.table1
on DB1.dbo.table1.col2 = DB2.dbo.table1.col2
where DB2.dbo.table1.col1 like
'textstring'

When I run it I get this error:

Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'lobbysqldbrawtest' in sysservers. Execute
sp_addlinkedserver to add the server to sysservers.

I'm sure it's because I'm using a 4-part name (Db2.dbo.table1.col1) and it's
trying to resolve the database as a server. I don't know of another way to
point to the database. Any suggestions?

Thanks in advance.

On Tue, 27 Mar 2007 19:03:20 -0700, John Steen
[quoted text, click to view]

Hi John,

I don't see any 'lobbysqldbrawtest' in the code you posted. May I assume
that you simplified the code?

[quoted text, click to view]

In column names, you don't repeat the server name. In fact, the database
name can be omitted as well; just the table name is enough - though I
personally prefer to use an alias. In this case, with the table names
being equal, you actually *need* to use an alias!

UPDATE dest
SET col1 = src.col1
FROM DB1.dbo.table1 AS src
INNER JOIN DB2.dbo.table1 AS dest
ON src.col2 = dest.col2
WHERE src.col1 LIKE 'textstring';
(untested)

And since there are no wildcards in 'textstring', you can replace LIKE
with = to gain some performance.

--
Hugo Kornelis, SQL Server MVP