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 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

Community Additions

ADD
Show: