Posted by Erland Sommarskog on 06/12/07 21:07

Zamdrist (zamdrist@gmail.com) writes:
> The answer was to create a table using Select Into containing the new
> value of the column I wanted, and to then do a straight up update
> column to column, i.e.
>
> Select mmatter, mdesc1 + ' (Stylized)' As mdesc
> Into CWStylized from matter
> Where (mmatter Like '10799.0062%'
> Or mmatter Like '10799.0063%'
> Or mmatter Like '10799.0061%')
> And Right(mmatter, 2) Not Like 'T[aA-zZ]'
> And Right(mmatter, 2) Not Like 'A[aA-zZ]'
> And mdesc1 Not Like '%(Stylized)%'
>
> Begin Transaction
> Update matter Set mdesc1 = CWStylized.mdesc
> From CWStylized
> Inner Join matter On matter.mmatter = CWStylized.mmatter
> Commit Transaction

I would expect fail for the same reason that the UPDATE failed. I ran:

CREATE TABLE textie (id int NOT NULL,
thetext text NULL)
go
INSERT textie(id, thetext)
VALUES (1, replicate('This is the original text', 2000))
go
SELECT id, thetext + '(Stylized)' AS ggg INTO newtextie FROM textie
go
SELECT datalength(thetext) FROM textie
SELECT datalength(thetext) FROM newtextie
go
DROP TABLE textie, newtextie

And I got:

(1 row(s) affected)
Msg 402, Level 16, State 1, Line 1
The data types text and varchar are incompatible in the add operator.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]