Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
You can update multiple base tables from a view. When your view combines two or more tables, you set properties to ensure that only the many side of the view query is updatable.
Views are updated on a table-by-table basis. You must ensure that for each table accessed in a view, the key field set is a unique key for both the view result set and the base table.
To make a multitable view updatable
In the Query and View Designers, choose the Update Criteria tab, and then select the tables and field names you want to update.
-or-
Use the DBSETPROP( ) Function.
In most cases, the default values provided by Visual FoxPro prepare a multitable view to be updatable, even when you create the view programmatically. The following code example creates and explicitly sets properties to update a two-table view. You can use this example as a guide for customizing update property settings on a view.
Updating Multiple Tables in a View
Code |
Comments |
|---|---|
CREATE SQL VIEW emp_cust_view AS ; SELECT employee.emp_id, ; employee.phone, customer.cust_id, ; customer.emp_id, customer.contact, ; customer.company ; FROM employee, customer ; WHERE employee.emp_id = customer.emp_id |
Create a view that accesses fields from two tables. |
DBSETPROP('emp_cust_view', 'View', 'Tables', 'employee, customer') |
Set the tables to be updated. |
DBSETPROP('emp_cust_view.emp_id', 'Field', ; 'UpdateName', 'employee.emp_id') DBSETPROP('emp_cust_view.phone', 'Field', ; 'UpdateName', 'employee.phone') DBSETPROP('emp_cust_view.cust_id', 'Field', ; 'UpdateName', 'customer.cust_id') DBSETPROP('emp_cust_view.emp_id1', 'Field', ; 'UpdateName', 'customer.emp_id') DBSETPROP('emp_cust_view.contact', 'Field', ; 'UpdateName', 'customer.contact') DBSETPROP('emp_cust_view.company', 'Field', ; 'UpdateName', 'customer.company') |
Set update names. |
DBSETPROP('emp_cust_view.emp_id', 'Field', ; 'KeyField', .T.) |
Set a single-field unique key for the Employee table. |
DBSETPROP('emp_cust_view.cust_id', 'Field', ; 'KeyField', .T.) DBSETPROP('emp_cust_view.emp_id1', 'Field', ; 'KeyField', .T.) |
Set a two-field unique key for the Customer table. |
DBSETPROP('emp_cust_view.phone', 'Field', ; 'Updatable', .T.) DBSETPROP('emp_cust_view.contact', 'Field', ; 'Updatable', .T.) DBSETPROP('emp_cust_view.company', 'Field', ; 'Updatable', .T.) |
Set the updatable fields. Typically, key fields are not updatable. |
DBSETPROP('emp_cust_view', 'View', ; 'SendUpdates', .T.) |
Activate the update functionality. |
GO TOP REPLACE employee.phone WITH "(206)111-2222" REPLACE customer.contact WITH "John Doe" |
Modify data in the view. |
TABLEUPDATE() |
Commit the changes by updating both the Employee and Customer base tables. |
See Also
Tasks
How to: Update a Table in a View