StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>As you stated, you either have to <strong>create FKs with an <code>ON DELETE CASCADE</code></strong> clause or to <strong>pre-delete the other rows</strong> with a subselect-delete.</p> <p>So, if you don't have an <code>ON DELETE CASCADE</code> clause you have to do</p> <pre><code>DELETE FROM ORDERS_ITEMS WHERE ORDER_ID in ( SELECT ORDER_ID FROM ORDERS WHERE STATUS = 'Canceled' ); DELETE FROM ORDERS_ADDRESS WHERE ORDER_ID in ( SELECT ORDER_ID FROM ORDERS WHERE STATUS = 'Canceled' ); DELETE FROM ORDERS WHERE STATUS = 'Canceled'; </code></pre> <p>It is simple, but is somewhat redundant so you may use the <a href="http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.apsg/db2z_createcte.htmstatement" rel="noreferrer" title="Creating a common table expression">WITH</a> statement. </p> <p>If the request to select the required rows is quite big, and <strong>if you don't have</strong> at least a <strong>RR isolation level</strong>, you may have to use a <a href="http://www.cs.newpaltz.edu/~pletcha/DB/db2_TempTables.html" rel="noreferrer" title="DB2 Temporary Tables"><strong>TEMPORARY table</strong></a> :</p> <pre><code>DECLARE GLOBAL TEMPORARY TABLE TMP_IDS_TO_DELETE (ID BIGINT) NOT LOGGED; INSERT INTO SESSION.TMP_IDS_TO_DELETE (ID) SELECT ORDER_ID FROM ORDERS WHERE STATUS = 'Canceled'; DELETE FROM ORDERS_ITEMS WHERE ORDER_ID in ( SELECT ID FROM SESSION.TMP_IDS_TO_DELETE ); DELETE FROM ORDERS_ADDRESS WHERE ORDER_ID in ( SELECT ID FROM SESSION.TMP_IDS_TO_DELETE ); DELETE FROM ORDERS WHERE ORDER_ID in ( SELECT ID FROM SESSION.TMP_IDS_TO_DELETE ); </code></pre> <p>This way you are sure that you will <strong>delete the same rows in each table</strong>, and a FK error will still fire up if you miss something. Per default, the temporary table will empty itself on commit.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload