Update a table by copying a column from another table

[Caroline]

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

[Simon Hayes]

"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