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/rbafzmstdelst.htm

333 lines
21 KiB
HTML
Raw Permalink Normal View History

Add readme and dist folders
2024-04-02 14:02:31 +00:00
<?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="DELETE statement, DELETE, SQL statements,
deleting, row, FROM clause, correlation-clause, in DELETE statement, table-name,
view-name, in FROM clause of DELETE, AS clause, correlation-name, WHERE clause,
with DELETE, search condition, WHERE CURRENT OF clause, cursor-name,
isolation-clause, UPDATE statement, WITH clause, delete rules, trigger, triggers,
referential integrity, check constraints, performance" />
<title>DELETE</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="delst"></a>
<h2 id="delst"><a href="rbafzmst02.htm#ToC_1008">DELETE</a></h2><a id="idx2360" name="idx2360"></a><a id="idx2361" name="idx2361"></a><a id="idx2362" name="idx2362"></a>
<p>The DELETE statement deletes rows from a table or view. Deleting
a row from a view deletes the row from the table on which the view is based
if no INSTEAD OF DELETE trigger is defined for 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> DELETE form is used to delete one
or more rows (optionally determined by a search condition).</li>
<li>The <span class="italic">Positioned</span> DELETE form is used to delete exactly
one row (as determined by the current position of a cursor).</li></ul>
<a name="wq1444"></a>
<h3 id="wq1444"><a href="rbafzmst02.htm#ToC_1009">Invocation</a></h3>
<p>A Searched DELETE statement can be embedded in an application program or
issued interactively. A positioned DELETE must be embedded in an application
program. Both Searched DELETE and Positioned DELETE are executable statements
that can be dynamically prepared.</p>
<a name="wq1445"></a>
<h3 id="wq1445"><a href="rbafzmst02.htm#ToC_1010">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 DELETE privilege on the table or view, 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">search-condition</span> in a Searched DELETE 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 <span class="italic">search-condition</span> includes a subquery, the privileges
held by the authorization ID of the statement must also include at least one
of the following: </p>
<ul>
<li>For each table or view identified in the subquery:
<ul>
<li>The SELECT privilege on the table or view, and</li>
<li>The system authority *EXECUTE on the library containing the table or view</li></ul></li>
<li>Administrative authority</li></ul>
<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="wq1446"></a>
<h3 id="wq1446"><a href="rbafzmst02.htm#ToC_1011">Syntax</a></h3>
<a href="rbafzmstdelst.htm#synsdelete"><img src="c.gif" alt="Click to skip syntax diagram" /></a>
<p><span class="bold">Searched DELETE:</span></p>
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn326.htm"
border="0" /></span><a href="#skipsyn-325"><img src="c.gif" alt="Skip visual syntax diagram"
border="0" /></a>>>-DELETE FROM--+-<span class="italic">table-name</span>-+--+--------------------+---------->
'-<span class="italic">view-name</span>--' '-<span class="italic">correlation-clause</span>-'
>--+-------------------------+--+------------------+----------->&lt;
'-WHERE--<span class="italic">search-condition</span>-' '-<span class="italic">isolation-clause</span>-'
</pre>
<a name="skipsyn-325" id="skipsyn-325"></a>
<p><span class="bold">Positioned DELETE:</span></p>
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn327.htm"
border="0" /></span><a href="#skipsyn-326"><img src="c.gif" alt="Skip visual syntax diagram"
border="0" /></a>>>-DELETE FROM--+-<span class="italic">table-name</span>-+--+--------------------+---------->
'-<span class="italic">view-name</span>--' '-<span class="italic">correlation-clause</span>-'
>--WHERE CURRENT OF--<span class="italic">cursor-name</span>------------------------------->&lt;
</pre>
<a name="skipsyn-326" id="skipsyn-326"></a>
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn328.htm"
border="0" /></span><a href="#skipsyn-327"><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-327" id="skipsyn-327"></a>
<a name="synsdelete"></a>
<h3 id="synsdelete"><a href="rbafzmst02.htm#ToC_1012">Description</a></h3>
<dl class="parml">
<dt class="bold">FROM <var class="pv">table-name</var> <span class="base">or</span> <var class="pv">view-name</var> </dt><a id="idx2363" name="idx2363"></a><a id="idx2364" name="idx2364"></a><a id="idx2365" name="idx2365"></a><a id="idx2366" name="idx2366"></a>
<dd>Identifies the table or view from which rows are to be deleted. 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 view
that is not deletable. For an explanation of deletable views, see <a href="rbafzmsthcview.htm#hcview">CREATE VIEW</a>.
</dd>
<dt class="bold"><var class="pv">correlation-clause</var> </dt><a id="idx2367" name="idx2367"></a><a id="idx2368" name="idx2368"></a>
<dd>Can be used within the <var class="pv">search-condition</var> to designate the table
or view and column names of the table or view. For an explanation of <var class="pv">correlation-clause</var>, see <a href="rbafzmstsubsel.htm#subsel">Queries</a>. For an explanation of <var class="pv">correlation-name</var>, see <a href="rbafzmstch2col.htm#c2cornm">Correlation names</a>.
</dd>
<dt class="bold">WHERE </dt><a id="idx2369" name="idx2369"></a><a id="idx2370" name="idx2370"></a>
<dd>Specifies the rows to be deleted. 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 deleted.
<dl class="parml">
<dt class="bold"><var class="pv">search-condition</var></dt>
<dd>Is any search condition as described in <a href="rbafzmstch2srch.htm#ch2srch">Search conditions</a>.
Each <var class="pv">column-name</var> in the <var class="pv">search-condition</var>, other than in
a subquery, must identify a column of the table or view.
<p>The <var class="pv">search-condition</var> is applied to each row of the table or view and the deleted rows are
those for which the result of the <var class="pv">search-condition</var> is 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 the subquery used in applying the <var class="pv">search
condition</var>. In actuality, a subquery with no correlated references may
be executed only once, whereas a subquery with a correlated reference may
have to be executed once for each row.</p>
<p>If a subquery refers to the object
table of the DELETE statement or a dependent table with a delete rule of CASCADE,
SET NULL, or SET DEFAULT, the subquery is completely evaluated before any
rows are deleted.</p>
</dd>
<dt class="bold">CURRENT OF <var class="pv">cursor-name</var> </dt><a id="idx2371" name="idx2371"></a><a id="idx2372" name="idx2372"></a>
<dd>Identifies the cursor to be used in the delete operation. The <var class="pv">cursor-name</var> must identify a declared cursor as explained in the Notes for the DECLARE
CURSOR statement.
<p>The table or view identified must also be specified in
the FROM clause of the <var class="pv">select-statement</var> of the cursor and the cursor
must be deletable. For an explanation of deletable cursors, see <a href="rbafzmsth2clcu.htm#h2dclcu">DECLARE CURSOR</a>.</p>
<p>When the DELETE statement is executed, the cursor must be positioned
on a row; that row is the one deleted. After the deletion, the cursor is positioned
before the next row of its result table. If there is no next row, the cursor
is positioned after the last row.</p>
</dd>
</dl>
</dd>
<dt class="bold"><var class="pv">isolation-clause</var> </dt><a id="idx2373" name="idx2373"></a>
<dd>Specifies the isolation level to be used for this statement.
<dl class="parml">
<dt class="bold">WITH <a id="idx2374" name="idx2374"></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="wq1447"></a>
<h3 id="wq1447"><a href="rbafzmst02.htm#ToC_1013">DELETE Rules</a></h3>
<p><span class="bold">Triggers:</span> <a id="idx2375" name="idx2375"></a><a id="idx2376" name="idx2376"></a>If the identified table or the base table of the
identified view has a delete trigger, the trigger is activated. A trigger
might cause other statements to be executed or raise error conditions based
on the deleted values.</p>
<p><span class="bold">Referential Integrity:</span> <a id="idx2377" name="idx2377"></a><a id="idx2378" name="idx2378"></a>If the
identified table or the base table of the identified table is a parent table,
the rows selected must not have any dependents in a relationship with a delete
rule of RESTRICT or NO ACTION, and the DELETE must not cascade to descendent
rows that have dependents in a relationship with a delete rule of RESTRICT
or NO ACTION.</p>
<p>If the delete operation is not prevented by a RESTRICT or NO ACTION delete
rule, the selected rows are deleted. Any rows that are dependents of the selected
rows are also affected: </p>
<ul>
<li>The nullable columns of the foreign keys of any rows that are their dependents
in a relationship with a delete rule of SET NULL are set to the null value.</li>
<li>The columns of the foreign keys of any rows that are their dependents
in a relationship with a delete rule of SET DEFAULT are set to the corresponding
default value.</li>
<li>Any rows that are their dependents in a relationship with a delete rule
of CASCADE are also deleted, and the above rules apply, in turn to those rows.</li></ul>
<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 delete, this would occur after all rows were
deleted and any associated triggers were activated.</p>
<p><span class="bold">Check Constraints:</span> <a id="idx2379" name="idx2379"></a><a id="idx2380" name="idx2380"></a>A check constraint
can prevent the deletion of a row in a parent table when there are dependents
in a relationship with a delete rule of SET NULL or SET DEFAULT. If deleting
a row in the parent table would cause a column in a dependent table to be
set to null or a default value and the null or default value would cause a
search condition of a check constraint to evaluate to false, the row is not
deleted.</p>
<p><span class="bold">DELETE Performance:</span> <a id="idx2381" name="idx2381"></a>An SQL DELETE statement that does
not contain a WHERE clause will delete all rows of a table. In this case,
the rows may be deleted using either a clear operation (if not running under
commitment control) or a change file operation (if running under commitment
control). If running under commitment control, the deletes can still be committed
or rolled back. This implementation will be much faster than individually
deleting each row, but individual journal entries for each row will not be
recorded in the journal. This technique will only be used if all the following
are true:</p>
<ul>
<li>The target table is not a view.</li>
<li>A significant number of rows are being deleted.</li>
<li>The job issuing the DELETE statement does not have an open cursor on the
file (not including pseudo-closed SQL cursors).</li>
<li>No other job has a lock on the table.</li>
<li>The table does not have an active delete trigger.</li>
<li>The table is not the parent in a referential constraint with a CASCADE,
SET NULL, or SET DEFAULT delete rule.</li>
<li>The user issuing the DELETE statement has *OBJMGT or *OBJALTER system
authority on the table in addition to the DELETE privilege.</li></ul>
<a name="wq1448"></a>
<h3 id="wq1448"><a href="rbafzmst02.htm#ToC_1014">Notes</a></h3>
<p><span class="bold">Delete operation errors:</span> If an error occurs while
executing any delete operation, changes from this statement, referential constraints,
and any triggered SQL statements are rolled back (unless the isolation level
is NC for this statement or any other triggered SQL statements).</p>
<p><span class="bold">Locking:</span> Unless appropriate locks already exist,
one or more exclusive locks are acquired during the execution of a successful
DELETE statement. Until the locks are released by a commit or rollback operation,
the effect of the DELETE operation can only be perceived by:</p>
<ul>
<li>The application process that performed the deletion</li>
<li>Another application process using isolation level UR or NC</li></ul><p class="indatacontent">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 <a href="rbafzmstisol.htm#isol">Isolation level</a>.</p>
<p>If an application process deletes a row on which any of its non-updatable
cursors are positioned, those cursors are positioned before the next row of
their result table. Let C be a cursor that is positioned before the next row
R (as the result of an OPEN, a DELETE through C, a DELETE through some other
cursor, or a Searched DELETE). In the presence of INSERT, UPDATE, and DELETE
operations that affect the base table from which R is derived, the next FETCH
operation referencing C does not necessarily position C on R. For example,
the operation can position C on R' where R' is a new row that is now the next
row of the result table.</p>
<p>A maximum of 4000000 rows can be deleted or changed in any single DELETE
statement when COMMIT(*RR), COMMIT(*ALL), COMMIT(*CS), or COMMIT(*CHG) was
specified. The number of rows changed includes any rows inserted, updated,
or deleted under the same commitment definition as a result of a trigger,
a CASCADE, SET NULL, or SET DEFAULT referential integrity delete rule.</p>
<p><span class="bold">Number of rows deleted:</span> When a DELETE statement is
completed, the number of rows deleted is returned in the ROW_COUNT condition
area item in the SQL Diagnostics Area (or SQLERRD(3) in the SQLCA). The value
in the ROW_COUNT item does not include the number of rows that were deleted
as a result of a CASCADE delete rule or a trigger.</p>
<p>For a description of the SQLCA, see <a href="rbafzmstsqlcca.htm#sqlcca">Appendix C. SQLCA (SQL communication area)</a>.</p>
<p><span class="bold">Referential integrity considerations:</span> The DB2_ROW_COUNT_SECONDARY
condition information item in the SQL Diagnostics Area (or SQLERRD(5) in the
SQLCA) shows the number of rows affected by referential constraints. It includes
rows that were deleted as the result of a CASCADE delete rule and rows in
which foreign keys were set to NULL or the default value as the result of
a SET NULL or SET DEFAULT delete rule.</p>
<p>For a description of the SQLCA, see <a href="rbafzmstsqlcca.htm#sqlcca">Appendix C. SQLCA (SQL communication area)</a>.</p>
<p><span class="bold">REXX:</span> Variables cannot be used in the DELETE statement
within a REXX procedure. Instead, the DELETE must be the object of a PREPARE
and EXECUTE using parameter markers.</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="wq1449"></a>
<h3 id="wq1449"><a href="rbafzmst02.htm#ToC_1015">Examples</a></h3>
<p><span class="italic">Example 1:</span> Delete department (DEPTNO) 'D11'
from the DEPARTMENT table. </p>
<pre class="xmp"> <span class="bold">DELETE FROM</span> DEPARTMENT
<span class="bold">WHERE</span> DEPTNO = 'D11'</pre>
<p><span class="italic">Example 2:</span> Delete all the departments from the
DEPARTMENT table (that is, empty the table). </p>
<pre class="xmp"> <span class="bold">DELETE FROM</span> DEPARTMENT</pre>
<p><span class="italic">Example 3:</span> Use a Java&trade; program statement to delete all the
subprojects (MAJPROJ is NULL) from the PROJECT table on the connection context
'ctx', for a department (DEPTNO) equal to that in the host variable HOSTDEPT
(java.lang.String).</p>
<pre class="xmp"> #sql [ctx] { <span class="bold">DELETE FROM</span> PROJECT
<span class="bold">WHERE</span> DEPTNO = :HOSTDEPT <span class="bold">AND</span> MAJPROJ <span class="bold">IS NULL</span> };</pre>
<p><span class="italic">Example 4:</span> Code a portion of a Java program
that will be used to display retired employees (JOB) and then, if requested
to do so, remove certain employees from the EMPLOYEE table on the connection
context 'ctx'. </p>
<pre class="xmp"> #sql iterator empIterator implements sqlj.runtime.ForUpdate
( ... );
empIterator C1;
#sql [ctx] C1 = { <span class="bold">SELECT * FROM</span> EMPLOYEE
<span class="bold">WHERE</span> JOB = 'RETIRED' };
#sql { <span class="bold">FETCH</span> C1 <span class="bold">INTO</span> ... };
while ( !C1.endFetch() ) {
System.out.println( ... );
...
if ( condition for deleting row ) {
#sql [ctx] { <span class="bold">DELETE FROM</span> EMPLOYEE
<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="idx2382" name="idx2382"></a><a id="idx2383" name="idx2383"></a>
<hr /><br />
[ <a href="#Top_Of_Page">Top of Page</a> | <a href="rbafzmstdeclvar.htm">Previous Page</a> | <a href="rbafzmstdescr.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 Copy Permalink