The definition of columns includes the option of using SQL statements to update the data in the column. These statements are placed in the Update Source and Where Clause fields. For the data to be successfully and correctly updated, you must complete these fields properly.
The general form for the SQL statement is:
SELECT ( update_source ) AS column_name FROM table_name A WHERE where_clause
Such that:
- The SELECT statement is legal and when run without the where_clause selects one and only one record for every record in the table_name.
- The table_name is the appropriate table being updated (as identified in the currently selected record in the top pane of the window you're using).
- The column_name is the appropriate column being updated (as identified in the currently selected record in the Columns pane of the window you're using).
- The update_source is the text in the UPDATE_SOURCE field. If filled, then this record in the Columns pane is a calculated column.
- The where_clause is the text in the WHERE_CLAUSE field. It need not be filled, but if filled, then the calculation is only applied to those records in the table that pass the where clause criteria.
Note: The best way to learn to write Update Source SQL is to find several existing examples and place them into the SELECT statement configuration shown above.
Use these tips to test your Update Source SQL
- To test if your update_source text is correct, place it into the SELECT statement configuration shown above without the where_clause. It should yield the same number of records as in the table with no duplication on the primary key or any unique index.
- To test if your where_clause text is correct, place it into the SELECT statement configuration shown above and see that it yields the expected filtered results.
