Re: HELP: problem updating table from table in same server
- From: Hugo Kornelis <hugo@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 28 Mar 2007 22:50:30 +0200
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
.
- Prev by Date: Re: "Order by" clause
- Previous by thread: Re: "Order by" clause
- Index(es):
Relevant Pages
|
|
