HELP: Updating column with data from another column

I post a message about this several months ago, but I may have misstated the
problem. So here goes again. My apologies for the duplicate.
I have two databases, each with identical schema. I need to update about
1,000 columns in DB1.table1, which are currently null, with data from the
same columns in DB2.table1. The tables essentially contain the same data,
except for the columns I need to update.
I'm using a unique ID column as the key. E.g., the value of the ID column
in DB1.table1.row1 is identical to the value of the ID in DB2.table1.row1.
I'm not the strongest at writing queries, but I tried using this query:
update DB1.dbo.table1
set DB1.dbo.table1.textcol = Db2.dbo.table1.textcol
where
(select IDcol from DB2.dbo.table1
where IDcol in (select IDcol from DB1.dbo.table1)
and DB2.dbo.table1.textcol like
'%textstring1'+char(13)+char(10)+'textstring2%'
It errored out with this:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near 'textstring2%'.
It seems like a simple thing to do. Can anyone shed some light on what I'm
doing wrong?
Thanks!
Johnupdate DB1.dbo.table1
set textcol = Db2.dbo.table1.textcol
from DB1.dbo.table1
inner join
DB2.dbo.table1
on DB1.dbo.table1.IDCol = DB2.dbo.table1.IDCol
where DB2.dbo.table1.textcol like
'%textstring1'+char(13)+char(10)+'textstring2%'
--
Russel Loski, MCSD.Net|||Thanks for the quick reply, Russell! That makes better sense. I ran the
query and am now getting this error:
Server: Msg 7202, Level 11, State 2, Line 2
Could not find server 'DB2' in sysservers. Execute sp_addlinkedserver to add
the server to sysservers.
Since DB2 is a database on the same server as DB1, and not a server, could I
be referring to the database incorrectly?
Thanks,
John
"RLoski" wrote:
> update DB1.dbo.table1
> set textcol = Db2.dbo.table1.textcol
> from DB1.dbo.table1
> inner join
> DB2.dbo.table1
> on DB1.dbo.table1.IDCol = DB2.dbo.table1.IDCol
> where DB2.dbo.table1.textcol like
> '%textstring1'+char(13)+char(10)+'textstring2%'
> --
> Russel Loski, MCSD.Net
>|||That sounds like you have two periods together or you have a four part
(db.owner.table.column) where a table is expected (which would be
interpretted as server.db.owner.table).
--
Russel Loski, MCSD.Net
"John Steen" wrote:
> Thanks for the quick reply, Russell! That makes better sense. I ran the
> query and am now getting this error:
> Server: Msg 7202, Level 11, State 2, Line 2
> Could not find server 'DB2' in sysservers. Execute sp_addlinkedserver to add
> the server to sysservers.
> Since DB2 is a database on the same server as DB1, and not a server, could I
> be referring to the database incorrectly?
> Thanks,
> John
>|||Thanks, Again, Russell. I'll check it out.
John
"RLoski" wrote:
> That sounds like you have two periods together or you have a four part
> (db.owner.table.column) where a table is expected (which would be
> interpretted as server.db.owner.table).
> --
> Russel Loski, MCSD.Net
>
> "John Steen" wrote:
> > Thanks for the quick reply, Russell! That makes better sense. I ran the
> > query and am now getting this error:
> >
> > Server: Msg 7202, Level 11, State 2, Line 2
> > Could not find server 'DB2' in sysservers. Execute sp_addlinkedserver to add
> > the server to sysservers.
> >
> > Since DB2 is a database on the same server as DB1, and not a server, could I
> > be referring to the database incorrectly?
> >
> > Thanks,
> > John
> >
>