SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Update Statement - Multiple Set Statements T-SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JamiBenson79
Starting Member

USA
5 Posts

Posted - 06/03/2009 :  09:54:29  Show Profile  Reply with Quote
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
3271 Posts

Posted - 06/03/2009 :  10:14:44  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30113 Posts

Posted - 06/03/2009 :  10:16:58  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

JamiBenson79
Starting Member

USA
5 Posts

Posted - 06/03/2009 :  10:33:02  Show Profile  Reply with Quote
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!
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 06/03/2009 :  10:37:40  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

JamiBenson79
Starting Member

USA
5 Posts

Posted - 06/03/2009 :  10:41:17  Show Profile  Reply with Quote
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
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 06/03/2009 :  10:43:19  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 06/03/2009 :  10:46:05  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

JamiBenson79
Starting Member

USA
5 Posts

Posted - 06/03/2009 :  10:58:07  Show Profile  Reply with Quote
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!
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 06/03/2009 :  11:04:33  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

JamiBenson79
Starting Member

USA
5 Posts

Posted - 06/03/2009 :  11:28:24  Show Profile  Reply with Quote
However you do it, I am grateful :-) Thanks again
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.33 seconds. Powered By: Snitz Forums 2000