cursors - 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

General SQL Server Forums

New to SQL Server Programming

cursors

Author

Topic

manny528
Starting Member

4 Posts

Posted - 2007-07-24 : 16:30:24

Can someone tell me what is wrong with this code?
I just want to get the account_number field from the accounts table and put it in the results table. Let me clarify, there are over 500 rows in the accounts table and I want to loop through those while setting the accounts.account_number = results.account_number.

declare @account nvarchar

DECLARE Accounts_Cursor CURSOR FOR
SELECT account_number FROM accounts
OPEN accounts_Cursor
FETCH NEXT FROM accounts_Cursor into @account
WHILE @@FETCH_STATUS = 0
BEGIN
update results
set account_number = @account
FETCH NEXT FROM accounts_Cursor into @account
END

CLOSE accounts_Cursor
DEALLOCATE accounts_Cursor

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-24 : 16:32:42

Don't you need a WHERE clause on your UPDATE statement if you are going to use a cursor?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

manny528
Starting Member

4 Posts

Posted - 2007-07-24 : 16:34:03

there are over 500 rows and I want to loop through when setting the account_number. don't i have to use a cursor for that?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-24 : 16:49:13

As Tara mentioned, you need to figure out the condition to UPDATE. Otherwise you will end up updating the entire table and create a mess (if you have not already done so).

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-24 : 16:49:41

quote:
Originally posted by manny528

there are over 500 rows and I want to loop through when setting the account_number. don't i have to use a cursor for that?

Nope, you typically do this type of thing with one statement (update with a join).

How is your current code going to know which row in the results table to update?

If you could show us a data example then we can probably write the code for you. The data example should show about 5 rows from each table and show us exactly how the results table should be updated.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-24 : 16:52:26

How do you know which records in Results table to update?

E 12°55'05.76"
N 56°04'39.42"

manny528
Starting Member

4 Posts

Posted - 2007-07-24 : 16:55:23

Maybe I am just going about this all the wrong way. Let me tell you the end result I am looking for. I am playing in a test environment with this right now. But in production, we have a table that somehow got ssn's in the account_number field. The app (which we do not own) is supposed to automatically generate the account_numbers in increments of 10. I am testing to see if we can manually put in account_numbers without screwing something up. I can see that the last account_number given out by the app was 15320. So starting with 15330, I wanted set the account_numbers in those 500+ rows. Does that make sense?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-24 : 16:57:00

Please show us a data example of what the data looks like before the update runs and what it should look like after the update runs. Show us sample data from both tables. Please see my previous post for this same information.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

manny528
Starting Member

4 Posts

Posted - 2007-07-24 : 17:03:02

I created the accounts table that starts with 15330 and increments by 10, so the accounts table has only the one field called account_number. The table I am trying to change has the following fields: account_id, account_ts, account_number

Accounts Table
15330
15340
15350
...

Results table
1236, timestamp, 333445555, 2005-01-01, 2006-02-07, 75

I want
Results table
1236, timestamp, 15330, 2005-01-01, 2006-02-07, 75

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-24 : 17:08:58

So how would you know what to update? How would you know to update account_number to 15330 for the account_id = 1236? Why shouldn't it be 15340 or 15350 instead?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-24 : 17:23:16

[code]UPDATE r
SET r.Account_Number = 15320 + 10 * d.RecID
FROM Results AS r
INNER JOIN (
SELECT Account_ID,
ROW_NUMBER() OVER (ORDER BY Account_ID) AS RecID
FROM Results
) AS d ON d.Account_ID = r.Account_ID[/code]

E 12°55'05.76"
N 56°04'39.42"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources