Updating Text Fields

Jul 20, 2005

I am trying to update text fields where the field only has the value
"closed." So a field that has closed and anything else will not be
updated.


update dbo.Hist
set dbo.Hist.Notes = ''
FROM dbo.Hist
WHERE (Notes LIKE 'closed_')

update dbo.Hist
set dbo.Hist.Notes = ''
FROM dbo.Hist
WHERE (Notes LIKE 'closed%') and len(convert
(varchar(7000),notes)) = 7

Neither code works and i get this error: "Subquery returned more than
1 value. This is not permitted when the subquery follows =, !=, <, <=
, >, >= or when the subquery is used as an expression.
The statement has been terminated."

What's another way to make the update?

View 1 Replies


ADVERTISEMENT

Adding A Full Text Search Across Multiple Tables (with Text Fields)

Sep 7, 2007

Hi, i'm trying to do a full text search on my site to add a weighting score to my results.  I have the following database structure:
Documents: - DocumentID (int, PK) - Title (varchar) - Content (text) - CategoryID (int, FK)
Categories: - CategoryID (int, PK) - CategoryName (varchar)
I need to create a full text index which searches the Title, Content and CategoryName fields.  I figured since i needed to search the CategoryName field i would create an indexed view.  I tried to execute the following query:
CREATE VIEW vw_DocumentsWITH SCHEMABINDING ASSELECT dbo.Documents.DocumentID, dbo.Documents.Title, dbo.Documents.[Content], dbo.Documents.CategoryID, dbo.Categories.CategoryNameFROM dbo.Categories INNER JOIN dbo.Documents ON dbo.Categories.CategoryID = dbo.Documents.CategoryID
GOCREATE UNIQUE CLUSTERED INDEX vw_DocumentsIndexON vw_Documents(DocumentID)
But this gave me the error:
Cannot create index on view 'dbname.dbo.vw_Documents'. It contains text, ntext, image or xml columns.
I tried converting the Content to a varchar(max) within my view but it still didn't like.
Appreciate if someone can tell me how this can be done as surely what i'm trying to do is not ground breaking.

View 2 Replies View Related

Replace Text In Text, Char && Varchar Fields All At Once?

Jan 22, 2008

I have followed many tutorials on selecting and replacing text in text fields, varchar fields and char fields, but I have yet to find a single script that will to all 3 based on field type. Let's assume for a moment that I don't know where all in my database a certain value that I need changed resides ... i.e., the data's tablename and fieldname. How would I go about doing the following ... or more importantly, is this even possible in a SQL only procedure?1) Loop over entire database and get all user tables2) Loop over all user tables and get all fields3) Loop over all fields and determine the field type4) switch between field types and change a string of text from 'a' to 'b'Please be gentle, I'm a procedure newb.

View 9 Replies View Related

Updating Multiple Fields

Sep 22, 2005

How can I update each record in a table, based on a value in another tablewith a single SQL statement?For example, suppose I have the following two tables:Table1Name Something Color-----------------------------------------John GHAS BlueJohn DDSS BlueJohn EESS BluePaul xxxx RedRingo HJKS RedRingo FFFS RedSara hjkd PurpleSara TTHE PurpleJimi sdkjls GreenTable2Name Color------------------------John ?Paul ?Ringo ?Sara ?Jimi ?How can I update the color field in table 2 to correspond with the colorfield in table1 (so I can normalize the db and delete the color field fromtable1)?I know I could open table2 and loop through within my app; just wonderingabout a single SQL statement that would do it. I need a similar technique inother places as part of my app.Thanks,Calan

View 5 Replies View Related

Updating Only Changed Fields

Sep 6, 2007

My client wants to minimize concurrency issues by only updating fields that the user actually changed. Whereas most update stored procedures I have created just updated all of the fields WHERE key=value, they want to update individual fields.

What I came up with so far does not look very efficient and I was wondering if anyone has suggestions for improvement.

Here is a snippet from the stored proc script:



Code Snippet
CREATE PROCEDURE dbo.DriverSaveChangedColumns
@DriverNumber VarChar(10) OUTPUT,
@CompanyName VarChar(50),
@DriverName VarChar(50),
@Address1 VarChar(50),
...
IF @CompanyName <> @Org_CompanyName

BEGIN

UPDATE Driver
SET DR_CompanyName = @CompanyName
WHERE DR_DriverNumber = @DriverNumber AND DR_CompanyName = @Org_CompanyName
END

IF @DriverName <> @Org_DriverName

BEGIN

UPDATE Driver
SET DR_DriverName = @DriverName
WHERE DR_DriverNumber = @DriverNumber AND DR_DRiverName = @Org_DriverName
END
...



Any suggestions?

The only other thing I thought of is to forget the stored procedure and do inline SQL ... but I'd rather have a proc.

Thanks!

View 15 Replies View Related

SQL Server/ Text Fields - Cannot Add Free Text

Sep 25, 2007

I have a data table in my project that has a "text" (not varchar) field in it.  I am trying to load this field with little paragraphs of text for showing on my pages. How do I get the free text loaded into the table?  The database explorer and all the data grid controls cut the text off at one line.  There doesn't seem to be any way to get multiline text into this table.  

View 2 Replies View Related

Updating A Field With Info From Other Fields

May 8, 2008

I am trying to make a field that has info from othe fields

Table = Page0
Fields = LName, FName, SS

I want a new field (Folder) to be all three fields.. for example
LName= Smith
FName= John
SS= 1234

I want to update Folder = Smith_John_1234

View 5 Replies View Related

SQL Server 2012 :: XML - Updating Parts Of Fields

May 30, 2014

I have a table where one of the fields contains XML as in the following. Is there an SQL update statement that can change any occurrences of timezoneIdfrom 0 to 1, and timezone from America/New_York to America/Chicago and time to time - 1 hour?

<scheduleitem><schedule><frequency><weekly weeklyInterval="1"><WED/></weekly></frequency><startDate>2008-08-05</startDate><time>22:30:00</time><timezoneId>0</timezoneId><timezone>America/New_York</timezone></schedule></scheduleitem>

After the query is run, rows like the above would be changed to

<scheduleitem><schedule><frequency><weekly weeklyInterval="1"><WED/></weekly></frequency><startDate>2008-08-05</startDate><time>21:30:00</time><timezoneId>1</timezoneId><timezone>America/Chicago</timezone></schedule></scheduleitem>

View 6 Replies View Related

T-SQL (SS2K8) :: Updating Statement Depending On 2 Fields

Sep 30, 2015

I need to build an update query for all my article beginning with '0.%' for my varchar field refkey, but depending on some conditions.

For example.
Case st.base <> '' and st.qttbase <> 0
Theo refkey = B
Case st.cos <>'' and st.qttcos <> 0
Then refkey = C
Case st.refo <> '' and st.qttrefo <> 0
Then refkey = R

But my problem are That i can have Many combination on refkey, for example:

B,BC,BCR,C,CB,CBR,R...and so on.

Also i need to separate the letters with comma, like:

B
B,C
B,C,R
.....

View 6 Replies View Related

DB Engine :: Updating Table Fields With Multiple Target Records

Apr 30, 2015

I am trying to multiple update records in Table B from a single record in Table A.  To identify the records that need to be updated I used this:

Select
Table1.cfirstnameas'Table
1 First',Table1.clastnameas'Table
1 Last',Table1.ifamilyid,CR.icontactid,CR.lLiveswithStudent,

Table2.cfirstnameas'Table
2 First',Table2.cLastNameas'Table
2 Last',Table2.ilocationidas'Table
2 Location',Table1.iLocationIDas'Table
1 Location'

fromTable1

JoinTable3
CRonTable1.istudentid=CR.istudentid

JoinTable2
onCR.icontactid=Table2.iContactID

whereCR.lLivesWithStudent=1
andTable1.ifamilyid>0
andTable1.ilocationid<>Table2.iLocationIDandTable1.lCurrent=1

I need to update the ilocationid from Table 1 to all Table 2 records related to Table 1but there is no direct relation from Table 1 to Table 2.  I needed Table 3 to make the connection from Table 1 to 2.

View 2 Replies View Related

Updating Text Containing æ,ø Or å

Jul 20, 2006

Hi,I'm making a translation thingie, that contains several languages. One ofthem is Danish and in one of my update scripts, I've come upon somethingstrange.I have a text containing the letter "å" which is a special Danish letter(along with ø and æ). When I create the data base and inserts the defaultdata everything is fine. But if I use an update script like:UPDATE [dbo].[TextControlLanguage]SET [Text] = 'Skabelonen er måske ikke gemt. Vil du gemme den?'WHERE TextControlID = 1217AND LanguageID = 1the "å" in "måske" gets lost. Do I have to some alpha nummeric code?How come that when inserting the default data everything is fine, but whenupdating the "å" is removed?/Hansen

View 1 Replies View Related

Updating A Text Column

Sep 20, 2001

how can i update a column with datatype of text with a combination of columns having a datatype of float? do i convert the float columns to varchar/char/?? and/or can i convert the column i am updating?

thanks!

View 2 Replies View Related

Updating A Text Field

Apr 27, 2000

I try to update a field of text datatype using WRITETEXT statement. The information that I try to change has both the single(') and double (") quotes in it.
How can I get it done ?

Thanks
Pete

View 1 Replies View Related

Updating A Text Field

Feb 2, 2007

I have a complex issue that has several steps.

Question 1.
I need to be able to update the following path in the database, a text field

[Info] NumAttachments=1 [Attachments] Attachment1=65513|C:Program FilesHEATHEATSelfServiceattachmentsWinter019997.htm

I need to update the path only to H:2007|( Winter019997.htm)Filename as in database.

Question 2
This needs to be done automatically so when attachments are being added this updates. How can I do this?

Question 3
I have the attachments saved in this location C:Program Files etc… I need them moved to another location on the network. How can I do this?

View 4 Replies View Related

Updating From A Text File?

Jul 20, 2005

Hi All,I'm not that great with MS-SQL, as I never really have any occasion touse it. However, I need to get this one thing working and don't havea clue where to start.I have a comma-delimited file that's delivered to the server everynight that contains updates to one of the tables. I'm trying tocreate a DTS package that will read in the text file and update all ofthe records contained in it, rather than simply append them to thebottom of the table (resulting in duplicate entries).I know how to schedule it and such, but if anyone can give me sometips on the design of the DTS package, it would be much appreciated.Thanks!- Steve Osit

View 3 Replies View Related

Updating Table From Text File

Jul 23, 2001

I have portions of data coming in as text files containing new records and updates of existing records. The solution I've figured out till yet is to import a portion of data into some intermediate table and then run a stored procedure to migrate the data into the real table.
Any ideas how to do this in a more efficient way?
Thanks in advance,

Asnate

View 3 Replies View Related

Updating A Text Field In SQL Server

Jul 23, 2005

Hi,I have a website using a SQL Server database to store all of it's data.Retrieving data and writing basic data back is fine, however, when i goto update one table that has a text field in it the update fails ifthe amount of data being passed to the text field is too large.Is there a way around this or a particular update i should be using?Any information would be greatly appreciated.CheersBj

View 8 Replies View Related

Full Text Index Not Updating

Oct 19, 2007



I am tring to use full text indexing. I have created an index and catalog. I can search on stuff that was entered before I created the index using contains or freetext but if I search on anything afterwards the results come up blank. I have created the following database and tables. I am using sql express with advanced services. The primary key I went in after I created the tabled and modified the row to increment by 1



create database RSDB2

use rsdb2

create table support

(ftid int NOT NULL PRIMARY KEY,

problemId varchar(50) NOT NULL,

problemTitle varchar(50) NOT NULL,

problemBody varchar(max) NOT NULL,

lOne varchar(50),

lTwo varchar(50),

lThree varchar(50),

lFour varchar(50),)



create fulltext catalog RSCatalog AS DEFAULT

create unique index ui_Support on support(ftid)

create fulltext index on support(problemBody)

key index PK__support__7C8480AE on RSCatalog



insert into support(problemId, problemTitle, problemBody)

values('win1001','testing outt he database','testing out the databases full texting capabilities again.')

select * from support where freetext(problemBody, 'testing');

View 1 Replies View Related

Text Fields In Sp_makewebtask

Mar 2, 1999

Hey everyone,

using SQL 6.5 sp_makewebtask in a stored procedure I want to create a
press-info file.
My query calls four fields, a datum field, two varchar fields and a text
field, it's
the field "PrText" in my example:

EXEC sp_makewebtask @outputfile='F:EntwicklungRedaktionZeitwenden27.ht m',
@query='SELECT Datum, Ort, Headline, PrText
FROM news
WHERE ID = 27',
@templatefile='F:EntwicklungRedaktionZeitwendenTem plates.htm'

My question: Am I limited to 255 characters in this case? After 255
characters the text is cut off and
I have only a truncated text fragment.

Is there a possibility to get the whole text without using @blobfmt (I don't
want to use this parameter because
I want to have the datum data, the varchar headline AND the text in ONE
HTML-file.)

I have tried the same as dynamic query in ASP where I have no problem to get
the whole content of the text field, but I want
to create a static file because this information will be read often, but
won't be updated frequently
and I don't want to charge the server resources to much.

Has anybody an idea? Any help would be appreciated.


Thank You

Dorothee Wiethoff

View 1 Replies View Related

Union With Text Fields

Sep 23, 2004

I'm unable to run a Union query with text fields in it. I get the following error:

'The text, ntext, or image data type cannot be selected at DISTINCT'

Is there no way to run a UNion statement for two tables that have text fields in them??? Please help.

View 4 Replies View Related

SQL Server Text Fields

Mar 16, 2006

The Text datatype can hold several million characters
The size of a record can be no more than 8 KB in normal data.

Created a table with one text field. For some reason can only store 1K of data into it. Even if the 8K limit was a factor (which it shouldn't for a text field) I cannot add more than 1K of data into my text field. 1023 characters to be exact.

Why is SQL Server 2000 doing this and more importantly how to resolve?

View 8 Replies View Related

Jioning Text Fields Together In SQL

May 10, 2004

Hi, I'm sure that this is probably a really easy question to most of you, but I seem to be spending ages on it as I am new to SQL. how do I add the carraige return char between joining two text fields ie.

a_blah.blah+CHAR(13)+b_blah.blah

Thanks for your help.

View 14 Replies View Related

Concatenate Two Text Fields

Mar 29, 2006

New to using MS SQL server!
I have a requirements DB. Before I came on board, the DB was seeded with requirements text and a requirements ID in separate columns. I've been asked to append the ID in front of the text:

Text - The user shall be able....
ID - 1.01.01.
Combined - 1.01.01.The user shall be able....

I've created the following query that displays the columns:

CREATE VIEW dbo.ReqID
AS
SELECT RQREQUIREMENTS.ID, RQREQUIREMENTS.REQUIREMENTNAME,
RQUSERDEFINEDFIELDVALUES.FIELDVALUE

FROM RQREQUIREMENTS LEFT OUTER JOIN
RQUSERDEFINEDFIELDVALUES ON
RQREQUIREMENTS.ID =
RQUSERDEFINEDFIELDVALUES.REQUIREMENTID

WHERE (RQUSERDEFINEDFIELDVALUES.FIELDID = 171)

The Text field is REQUIREMENTNAME
The ReqId is FIELDVALUE

Should I create an UPDATE query based on this SELECT, updating the REQUIREMENTNAME column with FIELDVALUE + REQUIREMENTNAME?

Please advise - thanks!

View 8 Replies View Related

Text And Ntext Fields

Jul 23, 2005

I've just gotten my data into SQL Server, and changed the connection stringon the program.There was a memo field (Access 2000) and we put that in a text field (with adefault length of 16?).Now, when displaying the data online (through asp pages), there are bigwhite-space gaps in the pages (that display the memo field data), imageshave a "double" url(http://www.mysite.com/thisfolder/ht...thelinksite.com)there are lots of place with "&nbsp;".The field data has html tags in it, if that has anything to do with it.It seems like I've heard that there are issues with text fields. Is thiswhat they meant? Is there anything I can do?Thanks, JA

View 4 Replies View Related

Retrieve Text In 2 Fields

Dec 10, 2007

I have the following data.

CustomerID Address
1 Spencer St
2 Frank Way
3 Jolly Dr

and i need to extract the address as 2 seperate fileds like so,

CustomerID Street Suffix
1 Spencer St
2 Frank Way
3 Jolly Dr

keep in mind that the Customer table has 2 million records.

View 5 Replies View Related

Concatenating Text Fields?

Dec 18, 2007


Hi All,

I am trying to get at some text fields from an AS400(JBA) system. the problem i'm having is that for each new line of text on AS400 it creates a new row with a line number associated in the SQL table. When trying to export to excel this causes problems because i need the text in one cell not spread over 15/20 lines.

I need to create a query/function that will concatenate the text lines together. I'm fairly new to T-SQL so could do with some help.


Example:-
ID TxtLn Text








R262965
1
Text 1

R262965
2
Text 2

R262965
3
Text 3


What i'm aiming for is:-
ID ConcatText
R262965 Text1 Text2 Text3



Can anyone please help me or guide me towards a starting point.

Thanks

View 2 Replies View Related

Common(?) Problem For Updating A Text Field!

Apr 21, 2008

 Hello, I have an aspx page with a text box. The user may enter a large portion of text including symbols like ' or ". On form submitting I would like a database (text or varchar(MAX) ) field to be updated with the value of the text box...The query that I have created for this purpose is the following: "string query ="UPDATE Article SET A_Content='" + content + "' WHERE A_Id=" + id; As I said before the user may (and in most cases must) enter special characters like ' (example: bla bla blah it's very good bla bla blah).The problem is that ' or " close the query string (SQL) and interpreter things that I am reffering to a field's name instead of the fields value (I think) resulting the following error:The identifier that starts with...is too long.Maximum length is 128.Unclosed quotation mark after the character string ...I think that it must be a very common problem with many solutions but for some reason I cant find anything on the web.What is the common practice to overcome the problem?Is there a function that might transform text into SQL acceptable text?Should I create methods for Replacing ' with other characters like ^^^ (text can be quite big...)?Thanx for any suggestions! 

View 3 Replies View Related

Updating A BLOB Text Doesn't Work

Jun 28, 2006

Hi all,
I have the following weird situation.
I must update a "text" column in my mssql db with a string that is more than 10000 character long.
I know that "text" is able to store up to 2Gbyte of data, but what is happening?
that when i excecute the query (in PHP, using the odbc_exec function), my poor Apache got stuck!!!
and afterwards, i am not even able to access that field (i mean Apache go nut again).

Have you ever experienced somthing like that? Any suggestion?

thank you

View 1 Replies View Related

SQL - Updating Empty Text Field Is Enterring -1

Jul 20, 2005

I'm trying to do a simple update like I have done countless timesbefore.However, when I update the empty fields in this table it places a -1into the field rather than the enterred value.It is indexed with duplicates allowed. It says that zero lenght isset to yes.What can I do about this?if Request.form("ouserid") <> "" Thenvar2 = " and userid = '" & Request.form("ouserid") & "' "Elsevar2 = " and userid is null "End ifif Request.form("orepid") <> "" Thenvar1 = " repid = '" & Request.form("orepid") & "'"Elsevar1 = " repid is null "End if"Update [Website Settings] set userid = '" & REquest.form("userid") &"' and repid = '" & Request.form("repid") & "' where " & var1 & var2& var3Thanks!

View 2 Replies View Related

Text Formatting In SQL Database Fields

Nov 14, 2006

Hi,
I am saving a couple of paragraphs of text into a varchar(max) field in a SQL database table. But when I try to use display the information again, then all the linefeed and return characters seem to be removed. How do I keep this text formating in the database field?
I am sure that I am missing something simple here, but I can not figure out what. Can someone please help with some advise?
Thanks
RegardsJan

View 6 Replies View Related

Selecting NOT EXISTS With Text Fields...?

Jul 31, 2007

 
I have three user tables (identical structure) that holds customer information. Each has about 1200 records, give or take a dozen, 99% of which are identical. However, there are a few records in each table that differ. Im trying to select the differences in these tables using a query.Each customer has a custmer ID, but cust_id 1234 in table a may not be the same customer as cust_id 1234 in table b. Therefore, i am trying to compare using the cuLoginName field which is the username/email address text field. This is  what differentiates the records.
I have tried using
select cust_id, cuLoginNamefrom tblCustomerAwhere NOT exists (select cuLoginName from tblCustomerB)
to get the different records between the two tables (i.e. those in A but not in B), but even though there are two users in A that do not appear in B, the results are coming back with nothing. I am guessing this is a problem comparing on text fields?
What is an effective way around this problem?
Thanks

View 3 Replies View Related

BULK INSERT And Text Fields

May 30, 2004

Hi,

I am trying to do a bulk insert using a text file that is comma delimited.

In the text file there are fields which are surrounded by quotes and contain commas; these commans should not be considered delimiters.

Is there a way to tell the BULK INSERT statement not to treat these as delimiters?

thanks,

sivilian

View 2 Replies View Related

2 Parter: Constraints And Text Fields

Apr 17, 2001

in SQL 7, can i set up a constraint or rule where a data entered in [field 1, table a, database 1] has to exist in [field 3, table a, database 2]?
i know i can't set up a relationship, and i haven't been able to get a constraint or rule to work either.
if i can't do the above, what is the recomended way to do this?
i don't want to, nor should i have to duplicate the table.
also, when importing data from one sql server to another, any field that was a data type of text came across as null?
any help would be great

thanks

spiralstairs

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved