LEFT Operator 1

[timos]

Dear all,
I don't have much experience with SQL Server so...
I have this;
When I try to make a Select query using the LEFT Operator everything works fine. The problem is that I cannot use this operator in an Update query (I do not know the syntax and the Help Index didn't show me anything).

I write this command

UPDATE Cat_PartAttachment
SET LEFT(Location, 22) = N'./SupplierImages/EBO41'
WHERE LEFT(Location, 22) = N'./SupplierImages/EBO14')

The result is this error... "Incorrect syntax near the keyword 'LEFT'."

Any ideas ??

Thank you all for your time

Timos

 

[bperry]

I'm thinking that this will not be allowed.
ie You are basically trying to tell SQl to update only part of the location column. But when you are updating a column, you are basically updating the entire column value, not just the first 22 bytes.

So, you will have to drop the LEFT() function on the SET statement...
SET Location = N'./SupplierImages/EBO41'

You could leave the LEFT() of the Where predicate, if that helps narrow down the actual rows to be updated.

bp

 

[timos]

I think you are wright, so I'll work it that way and see what.
thanks a lot bperry

 

[tlbroadbent]

You can update a substring of a column. Here are two methods.

Use Left, Right and Len functions
UPDATE Cat_PartAttachment
SET Location = N'./SupplierImages/EBO41' +
Right(Location,len(Location)-22)
WHERE LEFT(Location, 22) = N'./SupplierImages/EBO14'

Use Stuff function: very simple!
UPDATE Cat_PartAttachment
SET Location = Stuff(Location, 1, 22, N'./SupplierImages/EBO41')
WHERE LEFT(Location, 22) = N'./SupplierImages/EBO14' Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[bperry]

>>You can update a substring of a column.
>>Here are two methods.

Terry, that's a good suggestion, wish I had thought of it. (At the time I don't think I was clearly seeing what he wanted to do.)

Although, in actuality, we are indeed updating 'the entire column', your suggestion accomplishes what <glaux> was trying to do.

bp