It often happens when sifting through records in a database during a maintenance run. You may discover the case of loose child records that were left behind with no links to the parent table records -- a result of overlooked database manipulation logic in the development of some new application. It's now time to delete these unlinked records as part of a clean-up task. Of course that should follow enforcing rules in the program logic and structure so as to prevent future deletion of linked records in improper order from bottom to top, child to parent. Next, the duty of the database administrator comes into play, which involves monitoring closely the integrity of the data following much database activity through the use of an application.

The following SQL examples modify the records within a table whereby the records meet some criteria from one or more other tables. The resulting list of primary key field values (IDs) are matched against the table's key field values that is being impacted by an update, delete or insert transaction.

The following example uses a table join to query related tables by complex critera. The query requests the database server to delete all records from table 'table_1' that match all record IDs of related table 'table_2' that have an empty 'field_1' value (NULL).

The following example uses a table join to query related tables by complex critera. The query requests the database server to update field 'field_1' of table 'table_1' records that match the resulting list of all record IDs where the 'field_1' value of 'table_1' does not equal the 'field_2' value of 'table_2'.

In the above examples only two tables were joined by a left join. However in other cases where the criteria to modify records involves data from many fields spanning numerous tables it will be necessary to join more tables as their linking field values relate to the conditions for modifying records. The example below depicts three tables joined by two left joins.

This query contains one additional criterion in comparison to the update query that precedes it. Table 'table_3' is joined to table 'table_2' by their key linking field 'key_id_3' which differs from the key field 'key_id' linking 'table_1' to 'table_2'. Table 'table_3' field 'field_3' must contain a value greater than '100' in conjunction to the unequal field value comparison for: 'field_1' from 'table_1' and 'field_2' from 'table_2'. The DISTINCT record list was used so that duplicate IDs would not be returned. We are only updating a single instance of the 'table_1' records matching the overall criteria.

An insert query can be useful when copying data from a similar table on the production database server to the testing database server so as to synchronize the data. Inserting records from a list comes in handy when copying data from a record comprised of field values from various linked tables through joins as in the update example above.

In the example below records are inserted into table 'table_1' from table 'table_2'. It is important that the field data types match otherwise the database server may throw a data-type mismatch error. This example is a practical approach to copying records from a table on one server to a table on another server when synchronizing data. A condition may be included to copy records that match specific criteria only.

Joins may be included to provide even more complex criteria in the where clause that spans more than one linked table as a basis for the data selection of record insertion. The example below inserts records into table 'table_1' with constituent field data from table 'table_2', table 'table_3' and a constant field value of '6' (no table source). The two tables are joined together by their linking key fields 'key_field' only where the 'table_2' field value for 'field_a' has a value (not empty) and where the 'table_3' field value for 'field_b' is not equal to '5'. This is an arbitrary example that includes more complex conditions and data insertion from different tables. This application can be expanded to include any number of linked tables in the selection of data.