Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations John Tel on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Update Query 1

Status
Not open for further replies.
MichaelF81

MichaelF81

Programmer
Sep 20, 2005
178
US
I have a field in a the 'Qustn' table that has a long string of data. I seemed of made a mistake is most of them but not all. The field is called 'QustnText'.

At the end of the field I forgot to add some text (the string goes as follows)
Please review the role description (<a href="#" onclick="MyWindow=window.open('roles/Project Management Senior Consultant.doc','MyWindow','toolbar=no,location=no,directories=no,status=no,menubar=no,scrollbars=yes,resizable=yes,width=600,height=700,left=400'); return false;">Click Here</a>), keeping in mind the individual for whom you've been asked to provide input.<br /><br />Please use the space below to discuss how this individual has fulfilled the primary responsibilities of this role. Has the individual been effective? Why or why not?
Click to expand...

At the the spot where input is, I sometimes forgot to add the rest of the text starting at '<br /><br /> or I forgot everything starting at imput all together.

What is the easiest way to run an update statement to correct this without manually doing this?




"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
Sort by date Sort by votes
also, all of the text is exactly the same after the .doc is specified.




"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
Upvote 0 Downvote
Can you post some example data (with almost all variations of what you forgot to put) and what you want at the end?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
I just double-checked. They all end at either 'input' or
'input.' With or without the period.

Please review the role description (<a href="#" onclick="MyWindow=window.open('roles/Project Management Senior Consultant.doc','MyWindow','toolbar=no,location=no,directories=no,status=no,menubar=no,scrollbars=yes,resizable=yes,width=600,height=700,left=400'); return false;">Click Here</a>), keeping in mind the individual for whom you've been asked to provide input
Click to expand...

or

Please review the role description (<a href="#" onclick="MyWindow=window.open('roles/Project Management Senior Consultant.doc','MyWindow','toolbar=no,location=no,directories=no,status=no,menubar=no,scrollbars=yes,resizable=yes,width=600,height=700,left=400'); return false;">Click Here</a>), keeping in mind the individual for whom you've been asked to provide input.
Click to expand...




"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
Upvote 0 Downvote
If ALL text after .doc is the same, try:
Code: 
DECLARE @addValue varchar(500)
SET @addValue = '''.doc'',''MyWindow'',''toolbar=no,location=no,directories=no,status=no,menubar=no,scrollbars=yes,resizable=yes,width=600,height=700,left=400'); return false;">Click Here</a>), keeping in mind the individual for whom you've been asked to provide input.<br /><br />Please use the space below to discuss how this individual has fulfilled the primary responsibilities of this role. Has the individual been effective? Why or why not?'


UPDATE MyFile SET MyField =
                          LEFT(MyField, CHARINDEX('.doc', MyField)-1)+@addValue
(not tested)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
OK, so in Query Analyzer I had to change the ' to " (except the first and last) and when I ran

Code: 
DECLARE @addValue varchar(500)
SET @addValue = '"".doc"",""MyWindow"",""toolbar=no,location=no,directories=no,status=no,menubar=no,scrollbars=yes,resizable=yes,width=600,height=700,left=400"); return false;">Click Here</a>), keeping in mind the individual for whom you"ve been asked to provide input.<br /><br />Please use the space below to discuss how this individual has fulfilled the primary responsibilities of this role. Has the individual been effective? Why or why not?'


UPDATE Qustn SET QustnText =
                          LEFT(QustnText, CHARINDEX(".doc", QustnText)-1)+@addValue

I get the following error

Server: Msg 207, Level 16, State 3, Line 5
Invalid column name '.doc'.
Click to expand...




"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
Upvote 0 Downvote
There are NO double quotes in the string just doubled single quotes :)
Code: 
'''
is not
'"
[code]
Here the correct (I think) string:
[code]
DECLARE @addValue varchar(500)
SET @addValue = '''.doc'',''MyWindow'',''toolbar=no,location=no,directories=no,status=no,menubar=no,scrollbars=yes,resizable=yes,width=600,height=700,left=400''); return false;">Click Here</a>), keeping in mind the individual for whom you''ve been asked to provide input.<br /><br />Please use the space below to discuss how this individual has fulfilled the primary responsibilities of this role. Has the individual been effective? Why or why not?'

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
Same Error




"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
Upvote 0 Downvote
This works for me:
Code: 
DECLARE @addValue varchar(8000)
SET @addValue = '''.doc'',''MyWindow'',''toolbar=no,location=no,directories=no,status=no,menubar=no,scrollbars=yes,resizable=yes,width=600,height=700,left=400''); return false;">Click Here</a>), keeping in mind the individual for whom you''ve been asked to provide input.<br /><br />Please use the space below to discuss how this individual has fulfilled the primary responsibilities of this role. Has the individual been effective? Why or why not?'
--- Test table. you don't need this
DECLARE @test TABLE (MyField varchar(8000))
INSERT INTO @Test VALUES ('Please review the role description (<a href="#" onclick="MyWindow=window.open(''roles/Project Management Senior Consultant.doc''')
SELECT * from @Test

UPDATE @Test SEt MyField = LEFT(MyField, CHARINDEX('.doc', MyField)-1)+@addValue
SELECT * from @Test

BTW Are you sure you have '.doc' in ALL records?


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
I am positive .doc is there, but it keeps trying to read .doc as a field instead of value




"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
Upvote 0 Downvote
Just copy and paste the query above in QA. Try it, If it works remove these lines:
Code: 
DECLARE @test TABLE (MyField varchar(8000))
INSERT INTO @Test VALUES .....
SELECT * from @Test
UPDATE @Test SEt MyField = LEFT(MyField, CHARINDEX('.doc', MyField)-1)+@addValue
SELECT * from @Test

and change query to be:
Code: 
UPDATE Qustn SET QustnText =
                          LEFT(QustnText, CHARINDEX('.doc', QustnText)-1)+@addValue
--- No double quotes here also
SELECT * from @Test

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
Code: 
DECLARE @test TABLE (MyField varchar(8000))
DECLARE @addvalue varchar (8000)
INSERT INTO @Test VALUES ('blah')
SELECT * from @Test
UPDATE @Test SEt MyField = LEFT(MyField, CHARINDEX('.doc', MyField)-1)+@addValue
SELECT * from @Test

gives the error

(1 row(s) affected)


(1 row(s) affected)

Server: Msg 536, Level 16, State 3, Line 5
Invalid length parameter passed to the substring function.
The statement has been terminated.

(1 row(s) affected)
Click to expand...




"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
Upvote 0 Downvote
Yep, that would be the result if you don't have .doc in the field BUT:
[qoute]
I am positive .doc is there, but it keeps trying to read .doc as a field instead of value
[/code]
:)


If you change it to :
Code: 
DECLARE @test TABLE (MyField varchar(8000))
DECLARE @addvalue varchar (8000)
SET @addvalue = [more blah]

INSERT INTO @Test VALUES ('blah.doc')
SELECT * from @Test
UPDATE @Test SET MyField = LEFT(MyField, CHARINDEX('.doc', MyField)-1)+@addValue
SELECT * from @Test



Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
Server: Msg 207, Level 16, State 3, Line 3
Invalid column name 'more blah'.
Click to expand...




"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
Upvote 0 Downvote
When I post for SQL Server I must read carefully what I wrote. Sorry about this:
Code: 
SET @addvalue = 'more blah'

ALL strings must be in single quotes. NOT OTHER is available :).
Sorry.


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
worked fine, got both results




"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
Upvote 0 Downvote
I asked someone to manually do this, it is done. Thanks though




"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

MathiasB
  • Question
Parameterised sql server query
Replies
8
Views
248
Chriss Miller
C
leifoet
  • Locked
  • Question
Error in Select query
Replies
14
Views
730
Andrzejek
Andrzejek
leifoet
  • Locked
  • Question
How can I update the input of a form (rows and columns) to the database in one query ?
Replies
1
Views
349
Chris Miller
Chris Miller
Cpreston
  • Locked
  • Question
Update with a month name 1
Replies
9
Views
960
gmmastros
gmmastros
WomanPro
  • Locked
  • Question
Insert into query adds records to unreadable format, how do I change that? 1
Replies
5
Views
531
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top