February 22, 2006 at 4:06 pm
I am having some trouble with column names and I was hoping someone could help me out.
This is my statement:
Update neda_new
set +[cost break ]+ ' + @myNewID + ' = [Cost Break],
[Value Cost ]+ ' + @myNewID + ' = [Value Cost],
[Msr Value ]+ ' + @myNewID + ' = [Msr Value]
where [part number] = @partnumber
and [myNewId] = @myNewId
from neda_newark_pricing
Basically I want to update the column that has the name of ie: 'cost break' plus the value of my variable.
My table neda_new has the following columns
[Part Number]
[Cost Break 1]
[Value cost 1]
[Msr Value 1]
[Cost Break 2]
[Value Cost 2]
etc.
Any suggestions on the correct syntax (if it is even possible) for this?
Thanks a million.
J
February 22, 2006 at 4:18 pm
You need dynamic SQL for that. But that isn't the real problem.
Do you have control over the design of this table ? If yes, you should consider normalizing it and removing the repeating groups.
February 22, 2006 at 4:22 pm
PW: Thank you for the response. I'm not really sure what you mean though. The above sql statement is part of a larger query involving cursors. I do have the ability to change the design of the table, but I actually need all those column names the way they are.
My original table has data in it like this:
Part Number Cost Break Cost Value
xyz 1 24.00
xyz 5 23.00
And I need it to all be on 1 line for each part number.
Anyway, thanks again.
Justyna
February 22, 2006 at 4:26 pm
You will need to use dynamic sql statements to do this.
something like
DECLARE @sql varchar(500)
SET @sql = 'SELECT * FROM ' + @tablename + ' WHERE ID = ' + @MyID
EXEC(@SQL)
or
DECLARE @sql varchar(500)
SET @sql = 'UPDATE ' + @tablename + ' SET [MyTextField] = ''' + @MyTextValue + ''' WHERE ID = ' + @MyID
EXEC(@SQL)
Also you will need to cast/convert the value columns to character data type, if they aren't already, to concatenate properly.
February 22, 2006 at 4:33 pm
Chip: That's just what I was beginning to realize! Thanks for the suggestion. I'll give it a go first thing tomorrow.
Regards,
Justyna
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply