build apps by user

Update Formula :
Changing Values in Existing Rows of Database Tables

Update formula is used when you want to change the values in the existing rows of data in the database tables.

Format (Syntax)

Update table(s) Set field1 = value1, field2 = value2, ... Where criteria
Update ClauseUpdate table(s)
It defines the list of tables that need to change values.
Set ClauseSet field1 = value1, field2 = value2, …
It defines the field and value pairs for updating the values.
Where ClauseWhere criteria
It defines the conditions to select the rows of data that need updating of values.

Example 1

Update Customer Set CustName = "Company ABC", Email = "abc@gmail.com" Where CustNo = "C0012"
It updates the values of Customer Name and Email in the Customer record which CustNo = “C0012”.

Example 2

Update Customer Set CustNo = "AM" + CustNo Where Business = "AutoMobile"
This example demonstrates the manipulation of data in Update formula. It adds the prefix “AM” to the value of the field CustNo for all Customers which are in the Automobile business.

Example 3

Update Customer, Invoice Set Invoice.Type = "AM" Where Customer.CustNo = Invoice.CustNo And Customer.Business = "AutoMobile"
This example demonstrates the update of values with two tables. Customer table and Invoice table are related together by the common field CustNo. It selects the rows of Invoice records which Customers belong to Automobile business, and then update Invoice Type to “AM”.

Example 4

Update Customer, Invoice Set Invoice.CustName = Left(Customer.CustName, 10) Where Customer.CustNo = Invoice.CustNo
This example demonstrates the copy of value from one table (Customer) to another table (Invoice) which are related together. It copies Customer Names from Customer table to Invoice table for all Invoice records. It also shows the manipulation of data with the SQL function Left().