Updating multiple rows without cursor desktopdating org

Rated 4.20/5 based on 963 customer reviews

By using the same mechanics, a SQL procedure can also define a result set and return it directly to the caller of the SQL procedure or to a client application.A cursor can be viewed as a pointer to one row in a set of rows.This isolates the update so that no client can see the updated documents until they are all processed, or an error stops the update operation.As promised in the My SQL Cursors and Loops article, this article explores the use of cursors and their role in stored procedure programming.Cursors are used by database programmers to process individual rows returned by database system queries.Cursors enable manipulation of whole result sets at once.

SQL select * from Employee 2 / ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION ---- -------------------- -------------------- --------- --------- ---------- ---------- --------------- 01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected.

In recent times I have seen a developer writing a cursor to update a table. We want to update the value from Table2 to Table1 for the rows where Col1 is 21 and 31. I resolved this by providing the alias name after update query:: UPDATE Table1 SET Col2 = t2.

When asked the reason was he had no idea how to use multiple tables with the help of the JOIN clause in the UPDATE statement. Additionally, we want to update the values of Col2 and Col3 only.

To begin with we'll declare a couple of variables and set their default values.

The first variable will hold the running total of Oscars while the second will be used to read each individual film's Oscars as the cursor moves through the record set.

Leave a Reply