468,458 Members | 1,773 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,458 developers. It's quick & easy.

Update a table by copying a column from another table

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
1 32059

"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.

Similar topics

2 posts views Thread by Mike Leahy | last post: by
16 posts views Thread by robert | last post: by
3 posts views Thread by Michel Esber | last post: by
reply views Thread by kmladenovski | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.