- Joined Deletes

Joined Deletes

[EE] A joined delete occurs when a user deletes a joined row. A joined row is a row containing data from multiple tables.

For example, a query like:

SELECT * FROM JETEMP.CUSTOMER JOIN JETEMP.CUSTLOC ON CLOCID = LOCID

...would result in joined rows.

Additional properties can be used to control join behavior. See the Join property group:

Restrictions

A joined row must contain all key fields for all of its tables – otherwise that row cannot be used for a joined delete.

Transactions

The SQL standard does not allow for deleting records from multiple tables with a single statement, therefore internally WOW splits the delete statement into multiple separate SQL statements and sends them all to the database. It is possible for the one statement to succeed but for the other statements to fail (a dropped network connection or an authorization error are two things that could cause one statement to work and then others to fail). When the first statement fails, WOW abandons the update and reports the error as usual. However, if a subsequent statement fails after the first statement succeeds, the database could be left in a corrupted state. For this reason you may wish to configure your application not to use joined deletes, or to use database transactions.

A transaction is a way of bundling multiple SQL statements into a single unit of work – that unit of work will either succeed or fail as a whole. If it fails then none of the statements in the transaction will have affected the database. Some databases do not support transactions, and other databases require special configuration before transactions can be used. Check with your database documentation to find out how to configure transactions on your database.

By default WOW will not use transactions for joined deletes. If you want WOW to issue your joined delete as a single transaction (which is recommended if your database supports transactions), you must use the Join property group to specify this:

Join { transactions: true; }

This property group should be placed in the properties field of the operation which selected the joined rows. Alternatively you can place this property group in the properties field of the application, where it will apply to all of the operations in that application.