UPDATE CURSOR - declaring and use.

[carol]

I need to do something relatively simple…

I need to update a table using a cursor. (I may have to create a
stored procedure for doing this…)

I need to declare an update cursor, fetch the cursor and update the
data (and presumably close the cursor and de-allocate it…

The update query is as follows… Would anyone there know how to
declare the cursor for update and use it?

UPDATE A
SET A.Field1 =
(SELECT B.Field1
FROM B INNER JOIN A ON A.id = B.id)

I need to know how to declare the cursor and fetch it.

Can anyone give me an example of the code I need for the SQL Server?

Thanks!

[David Portas]

It's usually best to avoid using cursors at all. If you post your DDL, some
sample data and show your required result someone should be able to suggest
a cursor-free alternative.

I think the basic syntax you are looking for is as follows

UPDATE A
SET A.col1 =
(SELECT B.col1
FROM B WHERE id = A.id)

assuming ID is unique in B.

--
David Portas
SQL Server MVP
--

[Carol Berry]

I appreciate your answer. Actually the syntax of the update query that
I have shown works fine.

What I need to do is update a table based on information from a second
table. Since the relationship in one-to-many, I can potentially have
many rows returned for the update query.

Unless there is some other way to update multiple records, I need to
declare an update cursor, and what I was hoping someone could tell me
was the exact syntax for it... I read the "Help" documentation and made
a few trys...but an example would really help.

Thanks!
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Carol Berry (ca***@123marbella.com) writes:

I appreciate your answer. Actually the syntax of the update query that
I have shown works fine.

What I need to do is update a table based on information from a second
table. Since the relationship in one-to-many, I can potentially have
many rows returned for the update query.

Unless there is some other way to update multiple records, I need to
declare an update cursor, and what I was hoping someone could tell me
was the exact syntax for it... I read the "Help" documentation and made
a few trys...but an example would really help.

There are. But for all these types of questions, your chances to get
relevant help improves if you include:

o CREATE TABLE statements for your table.
o INSERT statements with sample data.
o The desired result from the sample data.

This permits people to post a tested solution, and saves them from guessing
what you are really looking for.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[David Portas]

Yes you can use an UPDATE statement based on a one/many -to-many join
between tables. Here's an example from the Pubs database, which updates each
Author with his/her YTD sales.

ALTER TABLE Authors ADD ytd_sales INTEGER

UPDATE Authors
SET ytd_sales =
(SELECT COALESCE(SUM(T.ytd_sales),0)
FROM TitleAuthor AS A
JOIN Titles AS T
ON A.title_id = T.title_id
WHERE A.au_id = Authors.au_id)

If you require more help then, as Erland says, we really need more
information (http://www.aspfaq.com/5006).

--
David Portas
SQL Server MVP
--