Using a Cursor to update a feild value

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 2005 Forums

Transact-SQL (2005)

Using a Cursor to update a feild value

Author

Topic

bendertez
Yak Posting Veteran

94 Posts

Posted - 2010-07-01 : 10:50:23

Hi

I'm trying to use a cursor to update a field value to enable me to set it to something else from accounts that I have generated in a select statement.

I have only used Cursors in the past to update table with data from a number of fields and have had no problems doing this but this time I want to update a specific field with a value for the accounts that the cursor generates.

The update code I have created which is the problem is as follows:

OPEN ACT_CURSOR
WHILE 1=1
BEGIN
FETCH NEXT FROM ACT_CURSOR
INTO
@ACCOUNTID

UPDATE ACCOUNT set USERID = 'General'
Where ACCOUNTID = @ACCOUNTID
END

The @ACCOUNTID is the variable that holds the accountid from the cursor. I'm presuming that I have got the syntax wrong with this. When i run it the procedure just runs for ever.

Can anybody help and show me the error of my ways...!!

Thanks in avance.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-07-01 : 10:52:13

In most cases, we can write a set-based solution instead of a cursor.

Can you explain your entire requirement, there's probably a better set-based solution.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-01 : 11:12:23

When i run it the procedure just runs for ever.
--> WHILE 1=1

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

bendertez
Yak Posting Veteran

94 Posts

Posted - 2010-07-01 : 11:16:37

Hi Thanks for the reply

I'm trying to update a number of accounts to change a value of a field UserID from 'Training to 'General'

select accountid from Account where userid = 'Training'

The query itself is pretty straight forward and if it was on its own I could quite easily manage it with an update statement without a problem.
However I want to run the query against anumber of tables as they all contain the userid field and I wanted to update all fields in one go rather than run individual scripts which is why i wanted to possibly use something like a Cursor to update each table in one go.

Can this be done?

How would you approach this?

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-01 : 11:25:30

How many tables?
I would do:
update table1 set UserId='General' where UserId='Training'
update table2 set UserId='General' where UserId='Training'
update table3 set UserId='General' where UserId='Training'
update table4 set UserId='General' where UserId='Training'
update table5 set UserId='General' where UserId='Training'

five lines of code to update five different tables...

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

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-07-01 : 11:26:30

quote:
Originally posted by webfred

How many tables?
I would do:
update table1 set UserId='General' where UserId='Training'
update table2 set UserId='General' where UserId='Training'
update table3 set UserId='General' where UserId='Training'
update table4 set UserId='General' where UserId='Training'
update table5 set UserId='General' where UserId='Training'

five lines of code to update five different tables...

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

Exactly.
Question to OP: Any reason why you are checking the AccountID while updating?

bendertez
Yak Posting Veteran

94 Posts

Posted - 2010-07-01 : 11:39:13

Thanks WebFreb

My code is a lot more complicated than that and i'm running it over many accounts, my example was just general idea of what i'm doing.

I know I can do it individually but I was wondering if there was a more streamlined way of running it rather than running each query individually as there is an accountid and a userid in each table.

I just thought if I already had the accountid in a variable I could reuse it on all my tables rather than keep running the individual queries.

Tnanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-07-01 : 11:48:32

Maybe you can explain your requirement thoroughly, so that a complete solution can be provided.

If you wish to update only a few rows with particular AccountIDs, you can still create a temp table , insert all AccountIDs, and JOIN your individual tables with this temp table to do your updates.

bendertez
Yak Posting Veteran

94 Posts

Posted - 2010-07-01 : 12:01:50

Hi vijayisonly

The userid is used so I can retrieve the accounts from my cursor query:

select accountid from Account where userid = 'Training'
The above query is alot larger than the above but ultimately returns the accountid that i want to update

The below query then updates the actual accounts using the userid from the above query.
UPDATE ACCOUNT set USERID = 'General'
Where ACCOUNTID = @ACCOUNTID

I was hoping then just to add the following lines underneath as shown below:
OPEN ACT_CURSOR
WHILE 1=1
BEGIN
FETCH NEXT FROM ACT_CURSOR
INTO
@ACCOUNTID
UPDATE sysdba.ACCOUNTSUMMARY set SECCODEID = 'SYST00000001'
Where ACCOUNTID = @ACCOUNTID

UPDATE sysdba.DCLACCGAS set SECCODEID = 'SYST00000001'
Where ACCOUNTID = @ACCOUNTID

UPDATE sysdba.DCLACCELEC set SECCODEID = 'SYST00000001'
Where ACCOUNTID = @ACCOUNTID

UPDATE sysdba.CONTACT set SECCODEID = 'SYST00000001'
Where ACCOUNTID = @ACCOUNTID

END

I wanted to have one procedure that ran everything rather than having individual scripts to run.

I hope I have made myself clearer.

Thanks again

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-07-01 : 12:08:14

[code]CREATE TABLE #T (ACCOUNTID int)
INSERT INTO #T
select ACCOUNTID from Account where USERID = 'Training'

UPDATE A set A.USERID = 'General'
FROM ACCOUNT A INNER JOIN #T B ON A.ACCOUNTID = B.ACCOUNTID

UPDATE A set A.SECCODEID = 'SYST00000001'
FROM sysdba.ACCOUNTSUMMARY A INNER JOIN #T B ON A.ACCOUNTID = B.ACCOUNTID

UPDATE A set A.SECCODEID = 'SYST00000001'
FROM sysdba.DCLACCGAS A INNER JOIN #T B ON A.ACCOUNTID = B.ACCOUNTID

UPDATE A set A.SECCODEID = 'SYST00000001'
FROM sysdba.DCLACCELEC A INNER JOIN #T B ON A.ACCOUNTID = B.ACCOUNTID

UPDATE A set A.SECCODEID = 'SYST00000001'
FROM sysdba.CONTACT A INNER JOIN #T B ON A.ACCOUNTID = B.ACCOUNTID[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources