HELP: problem updating table from table in same server

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.
John
On Tue, 27 Mar 2007 19:03:20 -0700, John Steen
<moderndads(nospam)@.hotmail.com> wrote:

>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.
Hi John,
I don't see any 'lobbysqldbrawtest' in the code you posted. May I assume
that you simplified the code?

>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?
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
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis