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.db2_5.4.0.1/rbafzmstupdt.htm

476 lines
30 KiB
HTML
Raw Permalink Blame History

<?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 xmlns="http://www.w3.org/1999/xhtml" lang="en-US" xml:lang="en-us">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="dc.language" scheme="rfc1766" 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. -->
<meta name="dc.date" scheme="iso8601" content="2005-09-19" />
<meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" />
<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="keywords" content="UPDATE statement, UPDATE, SQL statements,
in UPDATE statement, table-name, view-name, FROM clause of UPDATE, AS clause,
correlation-name, SET clause, assignment-clause, column-name, ROW clause,
expression, NULL, DEFAULT, row-fullselect, WHERE clause, with UPDATE,
search condition, search-condition, WHERE CURRENT OF clause, cursor-name,
isolation-clause, WITH clause, update rules, checking of unique constraints,
effect of commitment control, unique index, effect on update, check constraint,
UPDATE rules, WITH CHECK OPTION clause of CREATE VIEW statement,
CHECK OPTION clause, WITH CHECK OPTION clause, views with WITH CHECK OPTION,
updating with WITH CHECK OPTION views, view, trigger, referential integrity,
closed by error, cursor, during UPDATE, error, entry changed by UPDATE,
SQLCA (SQL communication area), locking, SQL (Structured Query Language)" />
<title>UPDATE</title>
<link rel="stylesheet" type="text/css" href="ibmidwb.css" />
<link rel="stylesheet" type="text/css" href="ic.css" />
</head>
<body>
<a id="Top_Of_Page" name="Top_Of_Page"></a><!-- Java sync-link -->
<script language = "Javascript" src = "../rzahg/synch.js" type="text/javascript"></script>
<a name="updt"></a>
<h2 id="updt"><a href="rbafzmst02.htm#ToC_1376">UPDATE</a></h2><a id="idx3252" name="idx3252"></a><a id="idx3253" name="idx3253"></a>
<p>The UPDATE statement updates the values of specified columns
in rows of a table or view. Updating a row of a view updates a row of its
base table, if no INSTEAD OF UPDATE trigger is defined on this view. If such
a trigger is defined, the trigger will be executed instead.</p>
<p>There are two forms of this statement: </p>
<ul>
<li>The <span class="italic">Searched</span> UPDATE form is used to update one
or more rows (optionally determined by a search condition).</li>
<li>The <span class="italic">Positioned</span> UPDATE form is used to update exactly
one row (as determined by the current position of a cursor).</li></ul>
<a name="wq1824"></a>
<h3 id="wq1824"><a href="rbafzmst02.htm#ToC_1377">Invocation</a></h3>
<p>A Searched UPDATE statement can be embedded in an application program or
issued interactively. A Positioned UPDATE must be embedded in an application
program. Both forms are executable statements that can be dynamically prepared.</p>
<a name="wq1825"></a>
<h3 id="wq1825"><a href="rbafzmst02.htm#ToC_1378">Authorization</a></h3>
<p>The privileges held by the authorization ID of the statement must include
at least one of the following: </p>
<ul>
<li>For the table or view identified in the statement:
<ul>
<li>The UPDATE privilege on the table or view, or</li>
<li>The UPDATE privilege on each column to be updated, or</li>
<li>Ownership of the table; and</li>
<li>The system authority *EXECUTE on the library containing the table or view</li></ul></li>
<li>Administrative authority</li></ul>
<p>If the <span class="italic">expression</span> in the <var class="pv">assignment-clause</var> contains a reference to a column of the table or view, or if the <span class="italic">search-condition</span> in a Searched UPDATE contains a reference
to a column of the table or view, then the privileges held by the authorization
ID of the statement must also include one of the following: </p>
<ul>
<li>The SELECT privilege on the table or view</li>
<li>Administrative authority</li></ul>
<p>If the <span class="italic">search-condition</span> includes a subquery
or if the <span class="italic">assignment-clause</span> includes a <span class="italic">scalar-fullselect</span> or <span class="italic">row-fullselect</span>, see <a href="rbafzmstsubsel.htm#subsel">Queries</a> for an explanation of the authorization required for each
subselect.</p>
<p>For information on the system authorities corresponding to SQL privileges,
see <a href="rbafzmstgnt.htm#eqtablet">Corresponding System Authorities When Checking Privileges to a Table or View</a>.</p>
<a name="wq1826"></a>
<h3 id="wq1826"><a href="rbafzmst02.htm#ToC_1379">Syntax</a></h3>
<a href="rbafzmstupdt.htm#synsupdate"><img src="c.gif" alt="Click to skip syntax diagram" /></a>
<p><span class="bold">Searched UPDATE:</span>
</p>
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn397.htm"
border="0" /></span><a href="#skipsyn-396"><img src="c.gif" alt="Skip visual syntax diagram"
border="0" /></a>>>-UPDATE--+-<span class="italic">table-name</span>-+--+--------------------+--------------->
'-<span class="italic">view-name</span>--' '-<span class="italic">correlation-clause</span>-'
>--+-------------------------+--SET--<span class="italic">assignment-clause</span>---------->
+-OVERRIDING SYSTEM VALUE-+
'-OVERRIDING USER VALUE---'
>--+-------------------------+--+------------------+----------->&lt;
'-WHERE--<span class="italic">search-condition</span>-' '-<span class="italic">isolation-clause</span>-'
</pre>
<a name="skipsyn-396" id="skipsyn-396"></a><p class="indatacontent">
<span class="bold">Positioned UPDATE:</span>
</p>
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn398.htm"
border="0" /></span><a href="#skipsyn-397"><img src="c.gif" alt="Skip visual syntax diagram"
border="0" /></a>>>-UPDATE--+-<span class="italic">table-name</span>-+--+--------------------+--------------->
'-<span class="italic">view-name</span>--' '-<span class="italic">correlation-clause</span>-'
>--+-------------------------+--SET--<span class="italic">assignment-clause</span>---------->
+-OVERRIDING SYSTEM VALUE-+
'-OVERRIDING USER VALUE---'
>--WHERE CURRENT OF--<span class="italic">cursor-name</span>------------------------------->&lt;
</pre>
<a name="skipsyn-397" id="skipsyn-397"></a><p class="indatacontent">
</p>
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn399.htm"
border="0" /></span><a href="#skipsyn-398"><img src="c.gif" alt="Skip visual syntax diagram"
border="0" /></a>assignment-clause::
.-,------------------------------------------------------------.
V |
|--+---+-<span class="italic">column-name</span>--&nbsp;=&nbsp;--+-<span class="italic">expression</span>-+-------------------------+-+-+--|
| | +-NULL-------+ | |
| | '-DEFAULT----' | |
| | .-,-----------. .-,--------------. | |
| | V | V | | |
| '-(----<span class="italic">column-name</span>-+--)--&nbsp;=&nbsp;--(--+---+-<span class="italic">expression</span>-+-+-+--)-' |
| | +-NULL-------+ | |
| | '-DEFAULT----' | |
| '-<span><span class="italic">row-fullselect</span></span>-----' |
| .-,--------------. |
| V | |
'-ROW--&nbsp;=&nbsp;--(--+---+-<span class="italic">expression</span>-+-+-+--)---------------------------'
| +-NULL-------+ |
| '-DEFAULT----' |
'-<span><span class="italic">row-fullselect</span></span>-----'
</pre>
<a name="skipsyn-398" id="skipsyn-398"></a><p class="indatacontent">
</p>
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn400.htm"
border="0" /></span><a href="#skipsyn-399"><img src="c.gif" alt="Skip visual syntax diagram"
border="0" /></a>isolation&ndash;clause:
|--WITH--+-NC-+-------------------------------------------------|
+-UR-+
+-CS-+
+-RS-+
'-RR-'
</pre>
<a name="skipsyn-399" id="skipsyn-399"></a>
<a name="synsupdate"></a>
<h3 id="synsupdate"><a href="rbafzmst02.htm#ToC_1380">Description</a></h3>
<dl class="parml">
<dt class="bold"><var class="pv">table-name</var><span class="base"> or </span> <var class="pv">view-name</var></dt><a id="idx3254" name="idx3254"></a><a id="idx3255" name="idx3255"></a>
<dd>Identifies the table or view to be updated. The name must identify a
table or view that exists at the current server, but it must not identify
a catalog table, a view of a catalog table, or a read-only view. For an explanation
of read-only views and updatable views, see <a href="rbafzmsthcview.htm#hcview">CREATE VIEW</a>.
</dd>
<dt class="bold"><var class="pv">correlation-clause</var> </dt><a id="idx3256" name="idx3256"></a><a id="idx3257" name="idx3257"></a>
<dd>Can be used within <var class="pv">search-condition</var> or <var class="pv">assignment-clause</var> to designate the table or view. For an explanation of <var class="pv">correlation-clause</var>, see <a href="rbafzmstsubselect.htm#tabref">table-reference</a>. For an explanation of <var class="pv">correlation-name</var>, see <a href="rbafzmstch2col.htm#c2cornm">Correlation names</a>.
</dd>
<dt class="bold">OVERRIDING SYSTEM VALUE or OVERRIDING USER VALUE</dt>
<dd>Specifies whether system-generated values or user-specified values for
a ROWID or identity column are used. If OVERRIDING SYSTEM VALUE is specified,
the implicit or explicit list of columns in the SET clause must contain a
column defined as GENERATED ALWAYS. If OVERRIDING USER VALUE is specified,
the implicit or explicit list of columns for the INSERT statement must contain
a column defined as either GENERATED ALWAYS or GENERATED BY DEFAULT.
<dl class="parml">
<dt class="bold">OVERRIDING SYSTEM VALUE</dt>
<dd>Specifies that the value specified in the SET clause for a column that
is defined as GENERATED ALWAYS is used. A system-generated value is not used.
</dd>
<dt class="bold">OVERRIDING USER VALUE</dt>
<dd>Specifies that the value specified in the SET clause for a column that
is defined as either GENERATED ALWAYS or GENERATED BY DEFAULT is ignored.
Instead, a system-generated value is used, overriding the user-specified
value.
</dd>
</dl>
<p>If neither OVERRIDING SYSTEM VALUE or OVERRIDING USER VALUE is
specified:</p>
<ul>
<li>A value cannot be specified for a ROWID or identity column that is defined
as GENERATED ALWAYS.</li>
<li>A value can be specified for a ROWID or identity column that is defined
as GENERATED BY DEFAULT. If a value is specified, that value is assigned to
the column. However, a value in a ROWID column defined BY DEFAULT can be updated
only if the specified value is a valid row ID value that was previously generated
by DB2 UDB for z/OS or DB2 UDB for iSeries. When a value of an identity column defined BY DEFAULT
is updated, the database manager does not verify that the specified value is a unique value
for the column unless the identity column is the sole key in a unique constraint
or unique index. Without a unique constraint or unique index, the database manager can
guarantee unique values only among the set of system-generated values as long
as NO CYCLE is in effect.
<p>If a value is not specified the database manager generates
a new value.</p></li></ul>
</dd>
<dt class="bold">SET</dt><a id="idx3258" name="idx3258"></a>
<dd>Introduces the assignment of values to column names.
</dd>
<dt class="bold"><var class="pv">assignment-clause</var></dt><a id="idx3259" name="idx3259"></a>
<dd>
<dl class="parml">
<dt class="bold"><var class="pv">column-name</var> </dt><a id="idx3260" name="idx3260"></a>
<dd>Identifies a column to be updated. The <var class="pv">column-name</var> must identify
a column of the specified table or view, but must not identify a view column
derived from a scalar function, constant, or expression. A column must not
be specified more than once.
<p>For a Positioned UPDATE: </p>
<ul>
<li>If the UPDATE clause was specified in the SELECT statement of the cursor,
each column name in the SET list must also appear in the UPDATE clause.</li>
<li>If the UPDATE clause was not specified in the SELECT statement of the
cursor, the name of any updatable column may be specified.</li></ul>
<p>For more information, see <a href="rbafzmstintsel.htm#updateclause">update-clause</a>.</p>
<p>A view column derived from the same column as another column of the view can
be updated, but both columns cannot be updated in the same UPDATE statement.</p>
<p>If a list of <var class="pv">column-names</var> is specified, the number of <var class="pv">expressions</var>, NULLs, and DEFAULTS must match the number of <var class="pv">column-names</var>.</p>
</dd>
<dt class="bold">ROW</dt><a id="idx3261" name="idx3261"></a>
<dd>Identifies all the columns of the specified table or view. If a view
is specified, none of the columns of the view may be derived from a scalar
function, constant, or expression.
<p>The number of <var class="pv">expressions</var>,
NULLs, and DEFAULTs (or the number of result columns from a <var class="pv">row-fullselect</var>) must match the number of columns in the row.</p>
<p>For
a Positioned UPDATE, if the UPDATE clause was specified in the SELECT statement
of the cursor, each column of the table or view must also appear in the UPDATE
clause. For more information, see <a href="rbafzmstintsel.htm#updateclause">update-clause</a>.</p>
<p>ROW may
not be specified for a view that contains a view column derived from the same
column as another column of the view, because both columns cannot be updated
in the same UPDATE statement.</p>
</dd>
<dt class="bold"><var class="pv">expression</var></dt><a id="idx3262" name="idx3262"></a>
<dd>Specifies the new value of the column. The <var class="pv">expression</var> is any
expression of the type described in <a href="rbafzmstch2expr.htm#ch2expr">Expressions</a>. It
must not include an aggregate function.
<p>A <var class="pv">column-name</var> in an expression
must name a column of the named table or view. For each row updated, the value
of the column in the expression is the value of the column in the row before
the row is updated.</p>
<p>Each variable in the clause must identify a host
structure or variable that is declared in accordance with the rules for declaring
host structures and variables. In the operational form of the statement, a
reference to a host structure is replaced by a reference to each of its variables.
For further information on variables and structures, see <a href="rbafzmstch2refvar.htm#ch2host">References to host variables</a> and <a href="rbafzmstch2refvar.htm#hscpcj">Host structures</a>. If a host structure is specified, the keyword ROW must be
specified.</p>
</dd>
<dt class="bold">NULL </dt><a id="idx3263" name="idx3263"></a>
<dd>Specifies the new value for a column is the null value. NULL should
only be specified for nullable columns.
</dd>
<dt class="bold">DEFAULT</dt><a id="idx3264" name="idx3264"></a>
<dd>Specifies that the default value is assigned to a column. The value
that is used depends on how the column was defined, as follows:
<ul>
<li>If the WITH DEFAULT clause is used, the default used is as defined for
the column (see <span class="italic">default-clause</span> in <span class="italic">column-definition</span> in <a href="rbafzmsthctabl.htm#hctabl">CREATE TABLE</a>).</li>
<li>If the WITH DEFAULT clause or the NOT NULL clause is not used, the value
used is NULL.</li>
<li>If the NOT NULL clause is used and the WITH DEFAULT clause is not used
or DEFAULT NULL is used, the DEFAULT keyword cannot be specified for that
column.</li></ul>
</dd>
<dt class="bold"><var class="pv">row-fullselect</var></dt><a id="idx3265" name="idx3265"></a>
<dd>A fullselect that returns a single result row. The number of result
columns in the select list must match the number of <var class="pv">column-names</var> (or
if ROW is specified, the number of columns in the row) specified for assignment.
The result column values are assigned to each corresponding <var class="pv">column-name</var>. If the result of the fullselect is no rows, then null values are assigned.
An error is returned if there is more than one row in the result.
<p>The <var class="pv">row-fullselect</var> may contain references to columns of the target table
of the UPDATE statement. For each row updated, the value of such a column
in the expression is the value of the column in the row before the row is
updated.</p>
</dd>
</dl>
</dd>
<dt class="bold">WHERE </dt><a id="idx3266" name="idx3266"></a><a id="idx3267" name="idx3267"></a>
<dd>Specifies the rows to be updated. The clause can be omitted, or a <var class="pv">search-condition</var> or <var class="pv">cursor-name</var> can be specified. If the clause
is omitted, all rows of the table or view are updated.
<dl class="parml">
<dt class="bold"><var class="pv">search-condition</var></dt><a id="idx3268" name="idx3268"></a>
<dd>Is any search described in <a href="rbafzmstch2srch.htm#ch2srch">Search conditions</a>. Each <var class="pv">column-name</var> in the search condition, other than in a subquery, must name
a column of the table or view. When the search condition includes a subquery
in which the same table is the base object of both the UPDATE and the subquery,
the subquery is completely evaluated before any rows are updated.
<p>The <var class="pv">search-condition</var> is applied to each row of the table or view. The updated
rows are those for which the results of the <var class="pv">search-condition</var> are
true.</p>
<p>If the <var class="pv">search-condition</var> contains a subquery, the subquery
can be thought of as being executed each time the <var class="pv">search-condition</var> is
applied to a row, and the results of that subquery used in applying the <var class="pv">search-condition</var>. In actuality, a subquery with no correlated references
may be executed only once. A subquery with a correlated reference may have
to be executed once for each row.</p>
</dd>
<dt class="bold">CURRENT OF <var class="pv">cursor-name</var> </dt><a id="idx3269" name="idx3269"></a><a id="idx3270" name="idx3270"></a>
<dd>Identifies the cursor to be used in the update operation. The <var class="pv">cursor-name</var> must identify a declared cursor as explained in <a href="rbafzmsth2clcu.htm#h2dclcu">DECLARE CURSOR</a>.
<p>The table or view named must also be named in the FROM clause of the SELECT
statement of the cursor, and the result table of the cursor must not be read-only.
For an explanation of read-only result tables, see <a href="rbafzmsth2clcu.htm#h2dclcu">DECLARE CURSOR</a>.</p>
<p>When the UPDATE statement is executed, the cursor must be positioned
on a row; that row is updated.</p>
</dd>
</dl>
</dd>
<dt class="bold"><var class="pv">isolation-clause</var> </dt><a id="idx3271" name="idx3271"></a>
<dd>Specifies the isolation level to be used for this statement.
<dl class="parml">
<dt class="bold">WITH <a id="idx3272" name="idx3272"></a></dt>
<dd>
<p>Introduces the isolation level, which may be one of:</p>
<ul>
<li>
<span class="term">RR</span> Repeatable read</li>
<li>
<span class="term">RS</span> Read stability</li>
<li>
<span class="term">CS</span> Cursor stability</li>
<li>
<span class="term">UR</span> Uncommitted read</li>
<li>
<span class="term">NC</span> No commit</li></ul>
</dd>
</dl> If <var class="pv">isolation-clause</var> is not specified the default isolation
is used. See <a href="rbafzmstintsel.htm#isocl">isolation-clause</a> for a description of how the default is
determined.
</dd>
</dl>
<a name="wq1827"></a>
<h3 id="wq1827"><a href="rbafzmst02.htm#ToC_1381">UPDATE Rules</a></h3><a id="idx3273" name="idx3273"></a>
<p><span class="bold">Assignment:</span> Update values are assigned to columns
in accordance with the storage assignment rules described in <a href="rbafzmstch2bas.htm#ch2bas">Assignments and comparisons</a>.</p>
<p><span class="bold">Validity:</span> Updates must obey the following rules.
If they do not, or if any other errors occur during the execution of the UPDATE
statement, no rows are updated. </p>
<ul>
<li><span class="italic">Fullselects:</span> The <var class="pv">row-fullselect</var> or <var class="pv">scalar-fullselect</var> shall return no more than one row (SQLSTATE
21000).</li>
<li><span class="italic">Unique constraints and unique indexes:</span> <a id="idx3274" name="idx3274"></a><a id="idx3275" name="idx3275"></a><a id="idx3276" name="idx3276"></a>If the identified table, or the base table
of the identified view, has one or more unique indexes or unique constraints,
each row update in the table must conform to the limitations imposed by those
indexes and constraints (SQLSTATE 23505).
<p>All uniqueness checks are effectively
made at the end of the statement. In the case of a multiple-row UPDATE of
a column involved in a unique index or unique constraint, this would occur
after all rows were updated.</p></li>
<li><span class="italic">Check constraints:</span> <a id="idx3277" name="idx3277"></a><a id="idx3278" name="idx3278"></a>If the identified
table, or the base table of the identified view, has one or more check constraints,
each check constraint must be true or unknown for each row updated in the
table (SQLSTATE 23513).
<p>All check constraints are effectively validated
at the end of the statement. In the case of a multiple-row UPDATE, this would
occur after all rows were updated.</p></li>
<li><span class="italic">Views and the WITH CHECK OPTION:</span> If a view is
identified, the updated rows must conform to any applicable WITH CHECK OPTION
(SQLSTATE 44000). For more information, see <a href="rbafzmsthcview.htm#hcview">CREATE VIEW</a>. <a id="idx3279" name="idx3279"></a><a id="idx3280" name="idx3280"></a><a id="idx3281" name="idx3281"></a><a id="idx3282" name="idx3282"></a><a id="idx3283" name="idx3283"></a></li></ul>
<p><span class="bold">Triggers:</span> <a id="idx3284" name="idx3284"></a><a id="idx3285" name="idx3285"></a>If the identified table or the base table of the
identified view has an update trigger, the trigger is activated. A trigger
might cause other statements to be executed or raise error conditions based
on the updated values.</p>
<p><span class="bold">Referential Integrity:</span> <a id="idx3286" name="idx3286"></a><a id="idx3287" name="idx3287"></a>The value
of the parent key in a parent row must not be changed.</p>
<p>If the update values produce a foreign key that is nonnull, the foreign
key must be equal to some value of the parent key of the parent table of the
relationship.</p>
<p>The referential constraints (other than a referential constraint with a
RESTRICT delete rule) are effectively checked at the end of the statement.
In the case of a multiple-row UPDATE, this would occur after all rows were
updated.</p>
<a name="wq1828"></a>
<h3 id="wq1828"><a href="rbafzmst02.htm#ToC_1382">Notes</a></h3><a id="idx3288" name="idx3288"></a><a id="idx3289" name="idx3289"></a><a id="idx3290" name="idx3290"></a>
<p><span class="bold">Update operation errors:</span> If an update value violates
any constraints, or if any other error occurs during the execution of the
UPDATE statement and COMMIT(*NONE) was not specified, all changes made during
the execution of the statement are backed out. However, other changes in the
unit of work made prior to the error are not backed out. If COMMIT(*NONE)
is specified, changes are not backed out.</p>
<p>It is possible for an error to occur that makes the state of the cursor
unpredictable.</p>
<p><span class="bold">Number of rows updated:</span> When an UPDATE statement
completes execution, the number of rows updated is returned in the ROW_COUNT
statement information item in the SQL Diagnostics Area (and SQLERRD(3) in
the SQLCA). For a description of the SQLCA, see <a href="rbafzmstsqlcca.htm#sqlcca">Appendix C. SQLCA (SQL communication area)</a>.</p>
<p><span class="bold">Locking:</span> Unless appropriate locks already exist,
one or more exclusive locks are acquired by the execution of a successful
UPDATE statement. Until these locks are released by a commit or rollback operation,
the updated rows can only be accessed by: </p>
<ul>
<li>The application process that performed the update.</li>
<li>Another application process using COMMIT(*NONE) or COMMIT(*CHG) through
a read-only cursor, SELECT INTO statement, or subquery.</li></ul><a id="idx3291" name="idx3291"></a>
<p>The locks can prevent other application processes from performing operations
on the table. For further information about locking, see the description of
the COMMIT, ROLLBACK, and LOCK TABLE statements, and isolation levels in <a href="rbafzmstisol.htm#isol">Isolation level</a>. Also, see the <a href="../dbp/rbafokickoff.htm">Database Programming</a> book.</p>
<p>A maximum of 500 000 000 rows can be updated or changed in any single UPDATE
statement when COMMIT(*RR), COMMIT(*ALL), COMMIT(*CS), or COMMIT(*CHG) has
been specified. The number of rows changed includes any rows inserted, updated,
or deleted under the same commitment definition as a result of a trigger.</p>
<p><span class="bold">REXX:</span> Variables cannot be used in the UPDATE statement
within a REXX procedure. Instead, the UPDATE must be the object of a PREPARE
and EXECUTE using parameter markers.</p>
<p><span class="bold">Datalinks:</span> If the URL value of a DATALINK column
is updated, this is the same as deleting the old DATALINK value then inserting
the new one. First, if the old value was linked to a file, that file is unlinked.
Then, unless the linkage attributes of the DATALINK value are empty, the specified
file is linked to that column.</p>
<p>The comment value of a DATALINK column can be updated without relinking
the file by specifying an empty string as the URL path (for example, as the
data-location argument of the DLVALUE scalar function or by specifying the
new value to be the same as the old value). If a DATALINK column is updated
with a null, it is the same as deleting the existing DATALINK value.</p>
<p>An error may occur when attempting to update a DATALINK value if the file
server of either the existing value or the new value is no longer registered
with the database server</p>
<p><span class="bold">Syntax alternatives:</span> The following keywords are synonyms
supported for compatibility to prior releases. These keywords are non-standard
and should not be used:</p>
<ul>
<li>The keyword NONE can be used as a synonym for NC.</li>
<li>The keyword CHG can be used as a synonym for UR.</li>
<li>The keyword ALL can be used as a synonym for RS.</li></ul>
<a name="wq1829"></a>
<h3 id="wq1829"><a href="rbafzmst02.htm#ToC_1383">Examples</a></h3>
<p><span class="italic">Example 1:</span> Change the job (JOB) of employee number
(EMPNO) '000290' in the EMPLOYEE table to 'LABORER'. </p>
<pre class="xmp"> <span class="bold">UPDATE</span> EMPLOYEE
<span class="bold">SET</span> JOB = 'LABORER'
<span class="bold">WHERE</span> EMPNO = '000290'</pre>
<p><span class="italic">Example 2:</span> Increase the project staffing (PRSTAFF)
by 1.5 for all projects that department (DEPTNO) 'D21' is responsible
for in the PROJECT table. </p>
<pre class="xmp"> <span class="bold">UPDATE</span> PROJECT
<span class="bold">SET</span> PRSTAFF = PRSTAFF + 1.5
<span class="bold">WHERE</span> DEPTNO = 'D21'</pre>
<p><span class="italic">Example 3:</span> All the employees except the manager
of department (WORKDEPT) 'E21' have been temporarily reassigned. Indicate
this by changing their job (JOB) to NULL and their pay (SALARY, BONUS, COMM)
values to zero in the EMPLOYEE table. </p>
<pre class="xmp"> <span class="bold">UPDATE</span> EMPLOYEE
<span class="bold">SET</span> JOB=<span class="bold">NULL</span>, SALARY=0, BONUS=0, COMM=0
<span class="bold">WHERE</span> WORKDEPT = 'E21' <span class="bold">AND</span> JOB &lt;> 'MANAGER'</pre>
<p><span class="italic">Example 4:</span> In a Java&trade; program display the rows from the
EMPLOYEE table on the connection context 'ctx' and then, if requested to do
so, change the job (JOB) of certain employees to the new job keyed in (NEWJOB). </p>
<pre class="xmp"> #sql iterator empIterator implements sqlj.runtime.ForUpdate
with( updateColumns='JOB' )
( ... );
empIterator C1;
#sql [ctx] C1 = { <span class="bold">SELECT * FROM</span> EMPLOYEE };
#sql { <span class="bold">FETCH</span> :C1 <span class="bold">INTO</span> ... };
while ( !C1.endFetch() ) {
System.out.println( ... );
...
if ( <span class="italic">condition for updating row</span> ) {
#sql [ctx] { <span class="bold">UPDATE</span> EMPLOYEE
<span class="bold">SET</span> JOB = :NEWJOB
<span class="bold">WHERE CURRENT OF</span> :C1 };
}
#sql { <span class="bold">FETCH</span> :C1 <span class="bold">INTO</span> ... };
}
C1.close();</pre><a id="idx3292" name="idx3292"></a><a id="idx3293" name="idx3293"></a><a id="idx3294" name="idx3294"></a>
<hr /><br />
[ <a href="#Top_Of_Page">Top of Page</a> | <a href="rbafzmstsignal.htm">Previous Page</a> | <a href="rbafzmstvaluestmt.htm">Next Page</a> | <a href="rbafzmst02.htm#wq1">Contents</a> |
<a href="rbafzmstindex.htm#index">Index</a> ]
<a id="Bot_Of_Page" name="Bot_Of_Page"></a>
</body>
</html>
Reference in New Issue View Git Blame Copy Permalink