update issue

Tauseef A

hi guys i want to upate sub string of one of my column update tale1 set substring(orderno,6,2) = 'tt' but above query is not working .

Tauseef A Khan MCP Dotnet framework 2.0.

Blue_Boy

Do you get error message? Explain more your question.

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com

David Mujica

Are you using transactions ? Make sure you have a "Commit" after your update statement. 2) Do you realize that in your example there is no "where" clause ? Every row in the table would be affected. I believe the root of your problem is that you can't have a function on the left side of the assignment operator in the update statement. The following code seems to work for me ...

select first_name from fsuser where user_code = 'DMUJICA'

begin transaction
update fsuser set first_name = substring(first_name,1,1) + 'XXX' +
substring(first_name,5,len(first_name)-4) where user_code = 'DMUJICA'

select first_name from fsuser where user_code = 'DMUJICA'
--commit
rollback

The output is: DAVID DXXXD

Abhishek Sur

This is weird... :confused::~:~ You are setting a constant term with another constant.. If you write Set substring(orderno, 6,2) = 'tt' It means if order no is 12345678 then you write 78 = tt... :omg: You can only apply use functions on the left hand side when you are using a comparison... In your case the query should be update tale1 set orderno = substring(orderno,1,6) + tt + substring(orderno,8, len(orderno) - 8) Remember, you are missing with where clause. So it will update every order no... :thumbsup::thumbsup:

Abhishek Sur

My Latest Articles **Create CLR objects in SQL Server 2005 C# Uncommon Keywords Read/Write Excel using OleDB

**Don't forget to click "Good Answer" if you like to.

Niladri_Biswas

Hi, I really cannot understand your requirement. What you are trying to achieve, is not at all clear in the statement presented here. I fully agree with Abhishek. However, if you invoke the statement "update tale1 set substring(orderno,6,2) = 'tt'" you will get the following error "Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ','." Now, I have a table say tblOrder with the following records.

OrderNo

123456789
234567891
345678912
45678123
67
78
89

My goal is to update those orderno's that will satisfy the condition substring(orderno,6,2). Means, if the order no is 987623456 , then substring('987623456',6,2) will yield me 34. So if any OrderNo 34 is present, that will be updated. The query is

update tblOrder set OrderNo = 'tt'

where OrderNo in( select substring(OrderNo,6,2) from tblOrder)

The output is

OrderNo

123456789
234567891
345678912
45678123
tt
tt
tt

Hope this helps :)

Niladri Biswas