| Author |
 Topic  |
|
|
JamiBenson79
Starting Member
USA
5 Posts |
 Posted - 06/03/2009 : 09:54:29
|
Hi all! Thanks for taking the time to read my post. I have a question about some T-SQL. In the C# program I am writing, I am dealing with a table that has a column that is a 95 character string. I am wanting to update only pieces of it. I get the first piece done using the wonderful stuff command. Works fine. I just use an update and set in SQL. My question is, is there a way to do multiple set statements on the same field or am I gonna have to write different methods for each update? I don't mind writing multiple methods, but if there is an easier way, I am all for it. I would be more than glad to provide any info you need. We use SQL Server 2003 if that matters. Again thanks for any help anyone can provide!!
Jami |
|
|
Lumbago
Norsk Yak Master
Norway
3241 Posts |
Posted - 06/03/2009 : 10:14:44
|
Not exactly sure what you mean but if you want to make multiple changes to one columns you need to do it in separate statements.
The following is not valid:
UPDATE mytable SET FirstName = REPLACE(FirstName, 'A', 'C'), FirstName = REPLACE(FirstName, 'n', 'm')
This would be the way to do it:
UPDATE mytable SET FirstName = REPLACE(FirstName, 'A', 'C')
UPDATE mytable SET FirstName = REPLACE(FirstName, 'n', 'm')
Not sure if this is what you're asking though...
- Lumbago |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 06/03/2009 : 10:16:58
|
There is no SQL Server 2003.
And no, multiple updates for same column are not allowed.DECLARE @Sample TABLE
(
data VARCHAR(13)
)
INSERT @Sample
SELECT 'Peter Larsson'
SELECT *
FROM @Sample
UPDATE @Sample
SET data = STUFF(data, 3, 8, ''),
data = STUFF(data, 5, 1, '')
SELECT *
FROM @Sample
E 12°55'05.63"
N 56°04'39.26" |
|
|
|
JamiBenson79
Starting Member
USA
5 Posts |
Posted - 06/03/2009 : 10:33:02
|
I apologize for my mistype on our SQL Server version. Point non withstanding, you are saying that you can NOT do multiple update statements to the same column at the same time correct? Then you do
UPDATE @Sample
SET data = STUFF(data, 3, 8, ''),
data = STUFF(data, 5, 1, '')
Ok. I try that same thing with this code
update BTSBControl
set Data = Stuff(data, 1,6, '060209'),
Data = stuff(data, 88, 6, 'BTSBUP')
where record = 'cc001'
I get an error in query analyzer that says, "Column name 'Data' appears more than once in the result column list."
I apologize for my lack of knowledge, but I have never had to do any updates like this one. I know you can do multiple sets on different columns in a row. Again thanks for your input. Thanks also Lumbago. If either of the two of you have any more thoughts, I'm appreciative. As I have said, I don't mind writing mulitple methods to do updates. I'm just trying to find shortcuts :)
Thanks again! |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 06/03/2009 : 10:37:40
|
Don't know but maybe nested?
SET data = STUFF((STUFF(data, 3, 8, '')),5,1,'')
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
JamiBenson79
Starting Member
USA
5 Posts |
Posted - 06/03/2009 : 10:41:17
|
Thanks so much for the thought webfred! Tried that and it's giving me an error about syntax error. Never thought about nesting it though. Thanks so much for the thought! You all are very helpful!
Jami |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 06/03/2009 : 10:43:19
|
DECLARE @Sample TABLE
(
data VARCHAR(13)
)
INSERT @Sample
SELECT 'Peter Larsson'
SELECT *
FROM @Sample
UPDATE @Sample
SET data = STUFF((STUFF(data, 3, 8, '')),5,1,'')
SELECT *
FROM @Sample
NO ERRORS
Greetings
Webfred
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 06/03/2009 : 10:46:05
|
Hi Peso,
now I can adress you like this: STUFF((STUFF('Peter Larsson', 3, 8, '')),5,1,'')
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
JamiBenson79
Starting Member
USA
5 Posts |
Posted - 06/03/2009 : 10:58:07
|
| Webfred, you are a genius! Once I figured out how to PROPERLY copy and paste, it worked like magic! was putting the column name twice instead of just once. That is going to save me sooooooooo much time. There's already like 6 rows to update so having to write 6 methods is better than 12 methods. I owe ya! Thanks again to all who made suggestions! Very glad I joined these forums! |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 06/03/2009 : 11:04:33
|
quote:
Originally posted by JamiBenson79
Webfred, you are a genius! Once I figured out how to PROPERLY copy and paste, it worked like magic! was putting the column name twice instead of just once. That is going to save me sooooooooo much time. There's already like 6 rows to update so having to write 6 methods is better than 12 methods. I owe ya! Thanks again to all who made suggestions! Very glad I joined these forums!
I'm not a genius 
But I'm able to copy from others and adapt the stuff 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
JamiBenson79
Starting Member
USA
5 Posts |
Posted - 06/03/2009 : 11:28:24
|
| However you do it, I am grateful :-) Thanks again |
|
|
| |
Topic |
|
|
|
Update Statement - Multiple Set Statements T-SQL
