Update a table by copying a column from another table -- sql server (alternate)

plize NO[at]SPAM letsdothatagain.com
11/28/2003 1:38:48 AM

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
)

Simon Hayes
11/29/2003 1:27:21 PM

[quoted text, click to view]

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

sql server (alternate) : Update a table by copying a column from another table