sql while loop, for loop ?? - 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 2008 Forums

Transact-SQL (2008)

sql while loop, for loop ??

Author

Topic

amanda
Starting Member

9 Posts

Posted - 2011-02-07 : 11:21:14

Dear Experts,
I’m trying to update a column based on it’s value
For example: if ID= ‘100200002’ then Id = 00 + column 2 value
If ID = ‘101200003’ then ID = 01 + column 2 value
I’m testing the second and the third digit of the column and then I will added with another field
My big problem is how to write SQL statement to iterate from the first record in the table to the last record ,
how can I use while loop or any loop in the query
and then how to compare and then update the field

I really really need your help urgently

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-02-07 : 11:46:13

You don't need a loop at all:

UPDATE myTable
SET ID=CASE 
WHEN ID='100200002' THEN '00' + col2
WHEN ID='101200003' THEN '01' + col2
ELSE ID END  -- this leaves all other IDs alone (sets them to their original value)
WHERE ID IN ('100200002', '101200003')

Just add WHEN clauses for each pattern you need to match, and additional values in the WHERE clause as needed.

amanda
Starting Member

9 Posts

Posted - 2011-02-07 : 16:17:47

I’m sorry I didn’t explain well
I just gave an example about the values of the ID column,
I want the loop to iterate each and every record in the table, then test the second and third digit of the ID column and last update its value (LPad with column 2 value )
I don't know how to make the loop, and I don't know how to update each column
please help

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-02-07 : 16:43:49

Can you provide more details, including any code you currently have? Please address your actual situation, with genuine examples and data (before and after).

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-07 : 17:30:41

You want this to update the actual ID column? Is the 2nd-3rd character sequence always going to be appended to col2 ? or only incertain conditions?

Update mytable
SET LPAD = SUBSTRING(ID,2,2) + Col2

Would take the 2nd and 3rd characters from ID column and append them to Col2.

adding

Where SUBSTRING(ID,2,2) IN ('00','01')

for example would only do it to rows meeting the specified condition in your example ..

Poor planning on your part does not constitute an emergency on my part.

amanda
Starting Member

9 Posts

Posted - 2011-02-08 : 00:47:34

yes,I want to

Update mytable
SET ID = SUBSTRING(ID,2,2) + Col2

to all the rows in the table,
would you please show me how to do it

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-08 : 00:54:25

the SQL statement in your last response is for all rows in the table Whereas, if you want to update specific rows then you will need to restrict the data set using Where condition .. e.g.

For All rows
Update mytable
SET ID = SUBSTRING(ID,2,2) + Col2

specific Rows
Update mytable
SET ID = SUBSTRING(ID,2,2) + Col2
Where SUBSTRING(ID,2,2) IN ('00','01')

amanda
Starting Member

9 Posts

Posted - 2011-02-08 : 01:22:57

You are right,
I was so confused, I started by declaring variables to read the values, then building begin- end blog…
And all along, it was very simple command…
Thank you all

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources