By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,919 Members | 1,043 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,919 IT Pros & Developers. It's quick & easy.

Update a table by copying a column from another table

P: n/a
I need to update a table by copying a column from another table
(having the same

structure, but on another database), from the record having the same
primary key.

1 - What is the correct query?

2 - I tried copying them record by record, but the datatype is ntext,
(it displays <long

text> in the result pane), and trying to update it results in the
following error

message:
The text, ntext, and image data types are invalid in this subquery or
aggregate

expression.

I tried variations of the following:

UPDATE TABLE
SET column0 = (
SELECT column0
FROM anotherDB.dbo.TABLE
WHERE anotherDB.dbo.TABLE.column1 = column1
)
WHERE anotherDB.dbo.TABLE.column1 = column1
Jul 20 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a

"Caroline" <pl***@letsdothatagain.com> wrote in message
news:da**************************@posting.google.c om...
I need to update a table by copying a column from another table
(having the same

structure, but on another database), from the record having the same
primary key.

1 - What is the correct query?

2 - I tried copying them record by record, but the datatype is ntext,
(it displays <long

text> in the result pane), and trying to update it results in the
following error

message:
The text, ntext, and image data types are invalid in this subquery or
aggregate

expression.

I tried variations of the following:

UPDATE TABLE
SET column0 = (
SELECT column0
FROM anotherDB.dbo.TABLE
WHERE anotherDB.dbo.TABLE.column1 = column1
)
WHERE anotherDB.dbo.TABLE.column1 = column1


This should work, assuming you always join on the primary key column:

update MyTable
set NtextColumn = ot.NtextColumn
from dbo.MyTable
join anotherDB.dbo.OtherTable ot
on MyTable.KeyColumn = ot.KeyColumn

This is MSSQL proprietary syntax, but the ANSI syntax would need a subquery,
and as you found, ntext columns are not allowed in subqueries.

Simon
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.