Solved

How to append data in SQL

Posted on 2014-09-12
9
141 Views
Last Modified: 2014-09-17
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
0
Comment
Question by:GiaHughes
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40318767
I think you want to update a column of a table, right?
Then you SQL statement should be something like:
UPDATE YourTableNameHere
SET FieldToBeUpdated = FieldToBeUpdated + TextToBeAppend
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'

Open in new window

0
 
LVL 2

Expert Comment

by:Akilandeshwari N
ID: 40318819
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'

Open in new window

0
 

Author Comment

by:GiaHughes
ID: 40318940
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
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40318945
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.
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40318948
Guy Hengel is right. Why you need a TEXT data type? How many characters are you expecting to store in that field?
0
 

Author Comment

by:GiaHughes
ID: 40318953
The WOC.TEXt is in a database which I cannot change

Gordon
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40319056
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
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40323385
If the SQL is at least 2005 version, you should be able to do this:


UPDATE WOC
 Set WOC.TEXTS = CAST(WOC.TEXTS AS varchar(max)) + 'Authorised by BJ'
 From WOC
 Inner Join WO On WOC.WONUM = WO.WONUM
 Where WO.CLOSEDATE = '1900-01-01 00:00:00.000' and
       WO.REQUESTDATE < '2014-01-01 00:00:00.000'
0
 

Author Closing Comment

by:GiaHughes
ID: 40327519
Works Great
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question