473,890 Members | 1,430 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

UPDATE CURSOR - declaring and use.

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!
Jul 20 '05 #1
4 30441
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
--
Jul 20 '05 #2
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!
Jul 20 '05 #3
Carol Berry (ca***@123marbe lla.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
Jul 20 '05 #4
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
--
Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2359
by: John Moore | last post by:
Hi, I normally work with Java but I'm interested in using Python as well, particularly for little tasks like doing some massaging of data in a MySQL database. Below is my first attempt. I'm sure it's inelegantly written, but my main concern is that the UPDATE sql doesn't actually work, and I can't understand why. No error is returned, it's just that the update does not take place. The SQL itself is fine, though - if I instead write the...
10
10912
by: technocrat | last post by:
Hi, I am trying to declare and cursor and thn load from that cursor into another table. Since I have almost 4 million records, I cant do it without the cursor which reduces the time by almost 1/10th. I tried to create a sql statement for "load from cursor cur insert into table name" using java stored procedure, but this isnt recognised by sql since load isnt a sql keyword. So whats the solution to this. I have to do it programatically...
5
34269
by: Paul M | last post by:
Hi All, I've been tasked with writing a z/OS C program to read and update a table (DB/2 v8). This Table contains a single row with a single column of data. This program (which will run as a Stored Proc) needs to implement a strategy for concurrency as the Stored Proc can be called concurrently by several different users. The code essentially does the following:
19
8390
by: Steve | last post by:
ASP error number 13 - Type mismatch with SELECT...FOR UPDATE statement I got ASP error number 13 when I use the SELECT...FOR UPDATE statement as below. However, if I use SELECT statement without FOR UPDATE, it is fine and no error. I also tried Set objRs = objConn.Execute("SELECT * FROM EMP UPDATE OF EMPNO"), but it still couldn't help. any ideas? I tried to search in the web but couldn't find similar
12
31017
by: Lucky | last post by:
Hi guys! i want to create one cursor in the t-sql. the problem is i want to use stored procedure instead of select command in cursor. can anyone tell me how can i use stored procedure's o/p to create cursor? i'm using sql 2000 and .net 2.0 thanks,
2
7735
by: mokazawa1 | last post by:
Hi, I'm using stored procedures in DB2 UDB 8.1.2. In this stored, I execute a select for update command, opening a cursor. Then I update the rows using fetch and current of. The problem is that I want to return the rows for my Java application. But if I set the cursor of the select for WITH RETURN, I get the values, but I cant execute the update. If I dont use WITH return, I execute the update, but I cant get the result set. Any ideas.
9
30997
by: Frank Swarbrick | last post by:
New to SQL here... We have a CURSOR declared thusly: EXEC SQL DECLARE ALL-ADJSTMTS-CSR CURSOR FOR SELECT ACCT.ACCOUNT_ID , ACCT.APPL_ID , ACCT.BRANCH_NUMBER , ACCT.CATEGORY_CODE
3
3989
by: Michel Esber | last post by:
Hi all, DB2 V8 LUW FP 15 There is a table T (ID varchar (24), ABC timestamp). ID is PK. Our application needs to frequently update T with a new value for ABC. update T set ABC=? where ID = ?
1
6790
by: vituko | last post by:
plpgsql (postgresql 8.3 but I can upgrade) I can open a cursor with a dynamic query (table / column variable) : -open cursor for execute '...' ; But if I want do updates... - execute 'update ...' where current of cursor ?? Mmm I don't find the way. Another approach :
0
9979
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11234
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10829
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10925
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9640
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8018
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7171
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
2
4276
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3282
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.