Update Statement - Multiple Set Statements T-SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update Statement - Multiple Set Statements T-SQL

Author

Topic

JamiBenson79
Starting Member

5 Posts

Posted - 2009-06-03 : 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

3271 Posts

Posted - 2009-06-03 : 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

30421 Posts

Posted - 2009-06-03 : 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

5 Posts

Posted - 2009-06-03 : 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
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-03 : 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

5 Posts

Posted - 2009-06-03 : 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
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-03 : 10:43:19

[code]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[/code]
NO ERRORS

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-03 : 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

5 Posts

Posted - 2009-06-03 : 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
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-03 : 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

5 Posts

Posted - 2009-06-03 : 11:28:24

However you do it, I am grateful :-) Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources