57 lines
4.0 KiB
HTML
57 lines
4.0 KiB
HTML
<?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="Example: Correlated subqueries in a DELETE statement" />
|
|
<meta name="abstract" content="When you use a correlated subquery in a DELETE statement, the correlation name represents the row you delete. SQL evaluates the correlated subquery once for each row in the table named in the DELETE statement to decide whether to delete the row." />
|
|
<meta name="description" content="When you use a correlated subquery in a DELETE statement, the correlation name represents the row you delete. SQL evaluates the correlated subquery once for each row in the table named in the DELETE statement to decide whether to delete the row." />
|
|
<meta name="DC.subject" content="correlation, subqueries, example DELETE statement, subquery, correlated, example DELETE statement, examples, correlated subquery, DELETE statement, DELETE statement, correlated subquery, use in" />
|
|
<meta name="keywords" content="correlation, subqueries, example DELETE statement, subquery, correlated, example DELETE statement, examples, correlated subquery, DELETE statement, DELETE statement, correlated subquery, use in" />
|
|
<meta name="DC.Relation" scheme="URI" content="rbafycorrs.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="rbafyexsub5" />
|
|
<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>Example: Correlated subqueries in a DELETE statement</title>
|
|
</head>
|
|
<body id="rbafyexsub5"><a name="rbafyexsub5"><!-- --></a>
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
|
<h1 class="topictitle1">Example: Correlated subqueries in a DELETE statement</h1>
|
|
<div><p>When you use a correlated subquery in a DELETE statement, the correlation
|
|
name represents the row you delete. SQL evaluates the correlated subquery
|
|
once for each row in the table named in the DELETE statement to decide whether
|
|
to delete the row.</p>
|
|
<div class="section"><p>Suppose a row in the CORPDATA.PROJECT table was deleted. Rows
|
|
related to the deleted project in the CORPDATA.EMPPROJACT table must also
|
|
be deleted. To do this, you can use: </p>
|
|
<pre> <strong>DELETE FROM</strong> CORPDATA.EMPPROJACT X
|
|
<strong>WHERE NOT EXISTS</strong>
|
|
(<strong>SELECT</strong> *
|
|
<strong>FROM</strong> CORPDATA.PROJECT
|
|
<strong>WHERE</strong> PROJNO = X.PROJNO)</pre>
|
|
</div>
|
|
<div class="section"><p>SQL determines, for each row in the CORPDATA.EMP_ACT table, whether
|
|
a row with the same project number exists in the CORPDATA.PROJECT table. If
|
|
not, the CORPDATA.EMP_ACT row is deleted.</p>
|
|
</div>
|
|
</div>
|
|
<div>
|
|
<div class="familylinks">
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="rbafycorrs.htm" title="You can write a subquery that SQL might need to re-evaluate as it examines each new row (WHERE clause) or group of rows (HAVING clause) in the outer-level SELECT. This is called a correlated subquery.">Correlated subqueries</a></div>
|
|
</div>
|
|
</div>
|
|
</body>
|
|
</html> |