Update 5 rows - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Update 5 rows

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-03-13 : 15:37:29

Hi, I have a table as the one below. I have a page that brings back all the users questions. I would liek to be able to update all 5 rows in the table when the user submits their changes.

How can I do this?

QID(int) UserID Response (varchar 300)

1 / 212 / This is my first response
2 / 212 / This is my second response
3 / 212 / This is my third response
4 / 212 / This is my fourth response
5 / 212 / This is my fifth response

Thanks again
Mike

Jay99

468 Posts

Posted - 2002-03-13 : 15:45:47

What does the next users answer look like in the table?

1 / 212 / This is my first response
2 / 212 / This is my second response
3 / 212 / This is my third response
4 / 212 / This is my fourth response
5 / 212 / This is my fifth response
1 / 213 / This is my first response
2 / 213 / This is my second response
3 / 213 / This is my third response
4 / 213 / This is my fourth response
5 / 213 / This is my fifth response

or

1 / 212 / This is my first response
2 / 212 / This is my second response
3 / 212 / This is my third response
4 / 212 / This is my fourth response
5 / 212 / This is my fifth response
6 / 213 / This is my first response
7 / 213 / This is my second response
8 / 213 / This is my third response
9 / 213 / This is my fourth response
10 / 213 / This is my fifth response

Jay

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-03-13 : 17:21:02

jay, it looks like this

1 / 212 / This is my first response
2 / 212 / This is my second response
3 / 212 / This is my third response
4 / 212 / This is my fourth response
5 / 212 / This is my fifth response
1 / 213 / This is my first response
2 / 213 / This is my second response
3 / 213 / This is my third response
4 / 213 / This is my fourth response
5 / 213 / This is my fifth response

thanks, mike

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-13 : 18:20:24

You can either:
create an sp to which you pass the user id, and their five responses
OR
call the update code 5 times....
OR
delete and re-insert

eg
for i = 1 to 5
sSQL = "update answers set response = '" & answer(i) & "'"
sSQL = " where UserId = " & iUserId & " and Qid = " & i
rst.Open sSQL, cn
next

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-03-13 : 18:23:39

Ok I would like to know how to do it via stored procedure..

How can I pass it 5 variables and have it update 5 rows at once?

I dont' want to loop thru any inserts since speed is a MAJOR consideration .. thanks

mike

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-13 : 18:32:29

in your page...
sSQL = "exec spAnswers " & iUserId
for i = 1 to 5
sSQL = sSQL & ",'" & answer(i) & "'"
next i
rst.Open sSQL, cn

then create your sp
create spAnswers
@userid int,
@answer1 nvarchar(300),
@answer2 nvarchar(300),
@answer3 nvarchar(300),
@answer4 nvarchar(300),
@answer5 nvarchar(300)
as

update answers
set response = @answer1
where userid = @userid and qid = 1

update answers
set response = @answer2
where userid = @userid and qid = 2

update answers
set response = @answer3
where userid = @userid and qid = 3

update answers
set response = @answer4
where userid = @userid and qid = 4

update answers
set response = @answer5
where userid = @userid and qid = 5

return
go

I mean, if you're really keen you could probably produce quite an elegant and generalised procedure using dynamic SQL and the whole bang - but there's no shame in having your solution implemented and working within 4 seconds either.....

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-03-14 : 02:08:45

thanks, worked perfectly!

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-03-14 : 08:49:56

Generalised, but maybe not eloquant:

create spAnswers
@userid int,
@answers nvarchar(4000),
@start_value int
as

DECLARE @counter int
DECLARE @answer_start int
DECLARE @answer_end int
DECLARE @final_answer nvarchar(4000)
SET @counter = 1
SET @answer_start = 1
SET @answer_end = CHARINDEX(',', @answers, 1)

WHILE @answer_end <> 0
BEGIN
SET @final_answer = LTRIM(SUBSTRING(@answers, @answer_start, (@answer_end)-(@answer_start)))
UPDATE answers
SET Response = @final_answer
WHERE UserId = @userid
AND QID = @start_value
SET @counter = @counter + 1
SET @start_value = @start_value + 1
SET @answer_start = @answer_end+1
SET @answer_end = CHARINDEX(',', @answers, @answer_start+1)
END
SET @answer_end = (LEN(@answers)-@answer_start+1)
SET @final_answer = LTRIM(SUBSTRING(@answers, @answer_start, @answer_end))
go

This one lets you specify as many values (up to 4000 at a time) in a CSV format for @answer parameter, as well the starting QID.

Jeremy

Edited by - joldham on 03/14/2002 11:42:16

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources