how to create a loop???

Question

how to create a loop???

soniaedis

SSC Rookie

Points: 34
More actions
November 3, 2013 at 2:03 pm

#282065

fairly new with advanced queries and need help.. how can I make the statement below automatic (loop) so I do not have to add rows everytime I use this? rows can vary from 10 to 200?
--UPDATE tmp_MT_SE_Upload
--SET page21.serial = CASE WHEN Row_ID = 1 THEN T.SERIAL
-- WHEN Row_ID = 2 THEN T.SERIAL + 1
-- WHEN Row_ID = 3 THEN T.SERIAL + 2
-- WHEN Row_ID = 4 THEN T.SERIAL + 3
-- WHEN Row_ID = 5 THEN T.SERIAL + 4
-- WHEN Row_ID = 6 THEN T.SERIAL + 5
-- WHEN Row_ID = 7 THEN T.SERIAL + 6
-- END
--FROM (SELECT TOP 1 MAX(serial+1) AS serial FROM page21) AS T

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

soniaedis (11/3/2013)
fairly new with advanced queries and need help.. how can I make the statement below automatic (loop) so I do not have to add rows everytime I use this? rows can vary from 10 to 200?
--UPDATE tmp_MT_SE_Upload
--SET page21.serial = CASE WHEN Row_ID = 1 THEN T.SERIAL
-- WHEN Row_ID = 2 THEN T.SERIAL + 1
-- WHEN Row_ID = 3 THEN T.SERIAL + 2
-- WHEN Row_ID = 4 THEN T.SERIAL + 3
-- WHEN Row_ID = 5 THEN T.SERIAL + 4
-- WHEN Row_ID = 6 THEN T.SERIAL + 5
-- WHEN Row_ID = 7 THEN T.SERIAL + 6
-- END
--FROM (SELECT TOP 1 MAX(serial+1) AS serial FROM page21) AS T

Well as posted this doesn't do anything as all the lines are commented out. 😀

Not quite sure what you are trying to do but a loop is absolutely not the answer. It is hard to tell which table you are trying to update. Are you trying to update tmp_MT_SE_Upload or page21?

In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements

2. Sample data in the form of INSERT INTO statements

3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

Why do you want a loop if you can do a very easy update without even needing a CASE?

UPDATE tmp_MT_SE_Upload

SET page21.serial = T.SERIAL + (Row_ID - 1)

FROM (SELECT TOP 1 MAX(serial+1) AS serial FROM page21) AS T

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

soniaedis SSC Rookie Points: 34 More actions · Answer 3

Luis,

I tried your formula and it worked perfectly.. Thank you ..