friedkiwi/ibm-information-center
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
master
ibm-information-center/dist/eclipse/plugins/i5OS.ic.sqlp_5.4.0.1/rbafyupdatingasretrieved.htm

144 lines
8.0 KiB
HTML
Raw Permalink Blame History

This file contains invisible Unicode characters

This file contains invisible Unicode characters that are indistinguishable to humans but may be processed differently by a computer. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html lang="en-us" xml:lang="en-us">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="security" content="public" />
<meta name="Robots" content="index,follow" />
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
<meta name="DC.Type" content="reference" />
<meta name="DC.Title" content="Update data as it is retrieved from a table" />
<meta name="abstract" content="You can update rows of data as you retrieve them by using a cursor." />
<meta name="description" content="You can update rows of data as you retrieve them by using a cursor." />
<meta name="DC.subject" content="examples, UPDATE statement, as data is retrieved, updating data as it is retrievied, example" />
<meta name="keywords" content="examples, UPDATE statement, as data is retrieved, updating data as it is retrievied, example" />
<meta name="DC.Relation" scheme="URI" content="rbafyupdate.htm" />
<meta name="DC.Relation" scheme="URI" content="rbafycurs.htm" />
<meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" />
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 1998, 2006" />
<meta name="DC.Format" content="XHTML" />
<meta name="DC.Identifier" content="rbafyupdatingasretrieved" />
<meta name="DC.Language" content="en-us" />
<!-- All rights reserved. Licensed Materials Property of IBM -->
<!-- US Government Users Restricted Rights -->
<!-- Use, duplication or disclosure restricted by -->
<!-- GSA ADP Schedule Contract with IBM Corp. -->
<link rel="stylesheet" type="text/css" href="./ibmdita.css" />
<link rel="stylesheet" type="text/css" href="./ic.css" />
<title>Update data as it is retrieved from a table</title>
</head>
<body id="rbafyupdatingasretrieved"><a name="rbafyupdatingasretrieved"><!-- --></a>
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
<h1 class="topictitle1">Update data as it is retrieved from a table</h1>
<div><p>You can update rows of data as you retrieve them by using a cursor.</p>
<div class="section"><p>On
the select-statement, use FOR UPDATE OF followed by a list of columns that
may be updated. Then use the cursor-controlled UPDATE statement. The WHERE
CURRENT OF clause names the cursor that points to the row you want to update.
If a FOR UPDATE OF, an ORDER BY, a FOR READ ONLY, or a SCROLL clause without
the DYNAMIC clause is not specified, all columns can be updated.</p>
</div>
<div class="section"><p>If a multiple-row FETCH statement has been specified and run,
the cursor is positioned on the last row of the block. Therefore, if the
WHERE CURRENT OF clause is specified on the UPDATE statement, the last row
in the block is updated. If a row within the block must be updated, the program
must first position the cursor on that row. Then the UPDATE WHERE CURRENT
OF can be specified. Consider the following example:</p>
</div>
<div class="tablenoborder"><table cellpadding="4" cellspacing="0" summary="" width="100%" frame="hsides" border="1" rules="rows"><caption>Table 1. Updating a table</caption><thead align="left"><tr><th align="left" valign="bottom" width="70.45454545454545%" id="d0e42">Scrollable Cursor SQL Statement</th>
<th align="left" valign="bottom" width="29.545454545454547%" id="d0e44">Comments</th>
</tr>
</thead>
<tbody><tr><td align="left" valign="top" width="70.45454545454545%" headers="d0e42 "> <p>EXEC SQL<br />
  <strong>DECLARE</strong> THISEMP <strong>DYNAMIC SCROLL CURSOR FOR</strong><br />
  <strong>SELECT</strong> EMPNO, WORKDEPT, BONUS<br />
     <strong>FROM</strong> CORPDATA.EMPLOYEE<br />
     <strong>WHERE</strong> WORKDEPT = 'D11'<br />
     <strong>FOR UPDATE OF</strong> BONUS<br />
END-EXEC.</p>
</td>
<td align="left" valign="top" width="29.545454545454547%" headers="d0e44 ">&nbsp;</td>
</tr>
<tr><td align="left" valign="top" width="70.45454545454545%" headers="d0e42 "> <p>EXEC SQL<br />
  <strong>OPEN</strong> THISEMP<br />
END-EXEC.</p>
</td>
<td align="left" valign="top" width="29.545454545454547%" headers="d0e44 ">&nbsp;</td>
</tr>
<tr><td align="left" valign="top" width="70.45454545454545%" headers="d0e42 "> <p>EXEC SQL<br />
  <strong>WHENEVER NOT FOUND</strong><br />
    GO TO CLOSE-THISEMP<br />
END-EXEC.</p>
</td>
<td align="left" valign="top" width="29.545454545454547%" headers="d0e44 ">&nbsp;</td>
</tr>
<tr><td align="left" valign="top" width="70.45454545454545%" headers="d0e42 "> <p>EXEC SQL<br />
  <strong>FETCH NEXT FROM</strong> THISEMP<br />
    <strong>FOR</strong> 5 <strong>ROWS</strong><br />
    <strong>INTO</strong> :DEPTINFO :IND-ARRAY<br />
END-EXEC.</p>
</td>
<td align="left" valign="top" width="29.545454545454547%" headers="d0e44 ">DEPTINFO and IND-ARRAY are declared in the
program as a host structure array and an indicator array.</td>
</tr>
<tr><td align="left" valign="top" width="70.45454545454545%" headers="d0e42 ">... determine if any employees in department
D11 receive a bonus less than $500.00. If so, update that record to the new
minimum of $500.00.</td>
<td align="left" valign="top" width="29.545454545454547%" headers="d0e44 ">&nbsp;</td>
</tr>
<tr><td align="left" valign="top" width="70.45454545454545%" headers="d0e42 "> <p>EXEC SQL<br />
  <strong>FETCH</strong> RELATIVE :NUMBACK <strong>FROM</strong> THISEMP<br />
END-EXEC.</p>
</td>
<td align="left" valign="top" width="29.545454545454547%" headers="d0e44 ">... positions to the record in the block
to update by fetching in the reverse order.</td>
</tr>
<tr><td align="left" valign="top" width="70.45454545454545%" headers="d0e42 "> <p>EXEC SQL<br />
  <strong>UPDATE</strong> CORPDATA.EMPLOYEE<br />
    <strong>SET</strong> BONUS = 500<br />
    <strong>WHERE CURRENT OF</strong> THISEMP<br />
END-EXEC.</p>
</td>
<td align="left" valign="top" width="29.545454545454547%" headers="d0e44 ">... updates the bonus for the employee in
department D11 that is under the new $500.00 minimum.</td>
</tr>
<tr><td align="left" valign="top" width="70.45454545454545%" headers="d0e42 "> <p>EXEC SQL<br />
  <strong>FETCH</strong> RELATIVE :NUMBACK <strong>FROM</strong> THISEMP<br />
    <strong>FOR</strong> 5 <strong>ROWS</strong><br />
    <strong>INTO</strong> :DEPTINFO :IND-ARRAY<br />
END-EXEC.</p>
</td>
<td align="left" valign="top" width="29.545454545454547%" headers="d0e44 ">... positions to the beginning of the same
block that was already fetched and fetches the block again. (NUMBACK -(5
- NUMBACK - 1))</td>
</tr>
<tr><td align="left" valign="top" width="70.45454545454545%" headers="d0e42 "> <p>... branch back to determine if any more
employees in the block have a bonus under $500.00.</p>
<p> ... branch back
to fetch and process the next block of rows.</p>
</td>
<td align="left" valign="top" width="29.545454545454547%" headers="d0e44 ">&nbsp;</td>
</tr>
<tr><td align="left" valign="top" width="70.45454545454545%" headers="d0e42 "> <p>CLOSE-THISEMP.<br />
EXEC SQL<br />
  <strong>CLOSE</strong> THISEMP<br />
END-EXEC.</p>
</td>
<td align="left" valign="top" width="29.545454545454547%" headers="d0e44 ">&nbsp;</td>
</tr>
</tbody>
</table>
</div>
</div>
<div>
<div class="familylinks">
<div class="parentlink"><strong>Parent topic:</strong> <a href="rbafyupdate.htm" title="This topic shows the basic SQL statement and clauses that update data into tables and views. To change the data in a table, use the UPDATE statement.">Change data in a table using the UPDATE statement</a></div>
</div>
<div class="relref"><strong>Related reference</strong><br />
<div><a href="rbafycurs.htm" title="When SQL runs a select statement, the resulting rows comprise the result table. A cursor provides a way to access a result table.">Use a cursor</a></div>
</div>
</div>
</body>
</html>
Reference in New Issue View Git Blame Copy Permalink