- Joined Updates

Joined Updates

[EE] A joined update occurs when a user edits and updates values in 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.

During a joined update the user is shown fields from all tables on the same screen. There is no indication given to

the user of which fields belong to which tables.

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

Restrictions

Each field name in a joined update must apply to a single table. When two or more tables have columns with the same name, those columns cannot be used in a joined update to those tables. In addition, the row being used for the joined update must contain all the key fields for all of the tables being jointly updated.

Transactions

The SQL standard does not allow for updating multiple tables with a single statement, therefore internally WOW splits the update statement into multiple separate SQL statements and sends them all to the database. It is possible for 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 updates, 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 updates. If you want WOW to issue your joined update 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.