How to: Update Multiple Tables in a View
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