Gordon Hughes
asked on
How to append data in SQL
I wish to create a query to append some text to a an existing table field with a where statement
The where statement would be
where WOC.WONUM = WO.WONUM and WO.CLOSEDATE = '1900-01-01 00:00:00.000' and
WO.REQUESTDATE < '2014-01-01 00:00:00.000'
Do I use APPEND as a command?
Gordon
The where statement would be
where WOC.WONUM = WO.WONUM and WO.CLOSEDATE = '1900-01-01 00:00:00.000' and
WO.REQUESTDATE < '2014-01-01 00:00:00.000'
Do I use APPEND as a command?
Gordon
UPDATE mytable
SET table_field = table_field + ISNULL(mytext,'')
FROM
mytable WOC,
mytable2 WO
WHERE WOC.WONUM = WO.WONUM and WO.CLOSEDATE = '1900-01-01 00:00:00.000' and
WO.REQUESTDATE < '2014-01-01 00:00:00.000'
ASKER
Hi
I have created the query
UPDATE WOC
Set WOC.TEXTS = WOC.TEXTS + 'Authorised by BJ'
From WOC,WO
where WOC.WONUM = WO.WONUM and WO.CLOSEDATE = '1900-01-01 00:00:00.000' and
WO.REQUESTDATE < '2014-01-01 00:00:00.000'
But got the following error
Msg 402, Level 16, State 1, Line 2
The data types text and varchar are incompatible in the add operator.
The WOC.TEXT field is (text, Null)
Gordon
I have created the query
UPDATE WOC
Set WOC.TEXTS = WOC.TEXTS + 'Authorised by BJ'
From WOC,WO
where WOC.WONUM = WO.WONUM and WO.CLOSEDATE = '1900-01-01 00:00:00.000' and
WO.REQUESTDATE < '2014-01-01 00:00:00.000'
But got the following error
Msg 402, Level 16, State 1, Line 2
The data types text and varchar are incompatible in the add operator.
The WOC.TEXT field is (text, Null)
Gordon
and that should be changed, unless you have some old sql server (or the db in a old compatibility version)
change the field to varchar(max) which has the same capacity as TEXT, but the normal operations than varchar.
change the field to varchar(max) which has the same capacity as TEXT, but the normal operations than varchar.
Guy Hengel is right. Why you need a TEXT data type? How many characters are you expecting to store in that field?
ASKER
The WOC.TEXt is in a database which I cannot change
Gordon
Gordon
updating TEXT columns is not so easy, and cannot be done in a UPDATE or JOIN.
see here for the syntax, please refer to the tech reference:
http://msdn.microsoft.com/en-us/library/ms189466.aspx
see here for the syntax, please refer to the tech reference:
http://msdn.microsoft.com/en-us/library/ms189466.aspx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Works Great
Then you SQL statement should be something like:
Open in new window