Use the UPDATE command to change the data in one or more columns in a table or a view.
Options
,
Indicates that this part of the command is repeatable.
(expression)
Determines a value using a text or arithmetic formula. The expression can include other columns from the table, constant values, functions, or system variables such as #date, #time, and #pi.
corr_name.
Correlation name. A nickname or alias for a table or view name. Use corr_name to refer to the same table twice within the command, or to explicitly specify a column in more than one table. The correlation name must be at least two characters.
FROM tblview
Specifies a list of tables or views from which data can be retrieved and updated.
NULL
Sets the values in the column equal to null.
SET colname
Specifies the column to update.
table
Specifies a table.
tblview
Specifies a table or view. If no table or view name is included, columns will be updated in all tables containing the specified columns, according to the conditions of the WHERE clause.
value
Specifies a value to enter in the specified column.
.varname
Specifies a global variable that provides a value for a column.
WHERE clause
Limits rows of data. For more information, see WHERE.
WHERE CURRENT OF cursor
Specifies a cursor that refers to a specific row to be affected by the UPDATE command. With this option, you must specify tblview.
About the UPDATE Command
The UPDATE command is useful for adjusting values in columns that require uniform changes.
The UPDATE command only modifies data in columns in one table or view. You can also update a table by referencing values from another table. You can modify a column's value by doing the following:
•Entering a new value for the column as a constant or variable
•Entering an expression that calculates a value for the column
•Entering a null value
Notes:
•Only users that have been granted rights to update the table(s) or column(s) can run the UPDATE command.
•R:BASE complies with defined rules, even for columns not affected by the update. If an update breaks a rule, the update is not processed.
•You cannot use UPDATE with computed or autonumbered columns. To change a computed column value, change the values in the columns to which the computed column refers.
•The UPDATE command will not update data in a multi-table view (a view based on multiple tables), as the data is not editable.
•A view with a GROUP BY parameter is also not editable.
Updating Column Values
You can update a column with a specific value. The value you use must meet the requirements of the column's data type, for example, a numeric column cannot be loaded with a text value.
Use the current delimiter character (the default is a comma) to separate each column and its new value from the next column and value.
Use the following guidelines when modifying data with UPDATE:
•Do not embed commas within entries for CURRENCY, DATE, DATETIME, DOUBLE, INTEGER, NUMERIC, or REAL data types. R:BASE automatically inserts commas and the current currency symbol.
•When values for CURRENCY, DOUBLE, NUMERIC, or REAL or data types are decimal fractions, you must enter the decimal point. When values are whole numbers, R:BASE adds a decimal point for you at the end of the number. R:BASE adds zeros for subunits in whole currency values. For example, using the default currency format, R:BASE loads an entry of 1000 as $1,000.00.
•When values for NOTE or TEXT data types contain commas, you can either enclose the entries within quotes, or use SET DELIMIT to change the default delimiter (comma) to another character.
•When values for NOTE or TEXT data types contain single quotes ('), and you are using the default QUOTES character ('), use two single quotes ('') in the text string. For example, 'Walter Finnegan''s order.'
•When values for NOTE or TEXT data types exceed the maximum length of a column, R:BASE truncates the value and adds it to the table. A message is displayed that tells you which row has been truncated.
Using an Expression or Variable
Enclose expressions in parentheses. If you use global variables in an expression, dot the variable (.varname). If expressions contain values that have a TEXT data type, enclose the values within quotes. The default QUOTES character is the single quote (').
If you attempt to use a null value in an expression or computed column, the result of the expression is null. However, if you set ZERO to on, R:BASE treats null values as zeros and processes expressions as if the null value were zero.
Using the WHERE Clause
If an UPDATE command includes a table or view name, you do not need to specify a WHERE or WHERE CURRENT OF clause. All rows will be updated. If you use a WHERE CURRENT OF clause, you must include a table or view name in the command.
If you omit a table or view name, you must use a WHERE clause with the UPDATE command so that you do not change values in more rows than you intended to change. The WHERE clause pinpoints the rows you want to change. If any columns exist in more than one table, all occurrences are changed if the column value meets the WHERE clause conditions. Test the WHERE clause by using the SELECT command before using the clause with UPDATE command. By using a WHERE clause with a SELECT command, you can view the rows you want to change before changing them.
R:BASE takes significantly less time to process a WHERE clause if one of the columns specified in the clause is an indexed column.
Using UPDATE with Transaction Processing
If more than one person at a time executes an UPDATE command and transaction processing is on, R:BASE might not execute the command concurrently. If you hold an UPDATE lock, you can read, modify, or delete any row in a table. R:BASE blocks any additional requests for UPDATE until other SELECT or UPDATE locks are cleared.
Examples
The following command changes values in the company and custphone columns of the customer table for the row where custid equals 100.
UPDATE customer SET company = 'Quality Computers', +
custphone = '617-341-3762' WHERE custid = 100
The following command changes the invoicetotal column in the transmaster table to the value of the expression (invoicetotal * .9) for rows where transid is greater than 5000.
UPDATE transmaster SET invoicetotal = ( invoicetotal * .9) +
WHERE transid > 5000
The following command changes the listprice column to the value of the expression (1.1 * listprice) for every row in the prodlocation table containing an entry in the listprice column.
UPDATE prodlocation SET listprice = (1.1 * listprice) +
WHERE listprice IS NOT NULL
The following command adds to the set of conditions in the above command. The command below extracts all of the selling prices from the transdetail table and requires that listprice be changed only if it matches a current selling price in the table.
UPDATE product SET listprice = (1.1 * listprice) +
WHERE listprice IS NOT NULL AND model = 'CX3000' +
AND listprice IN (SELECT price FROM transdetail +
WHERE model = 'CX3000')
The following command changes the onhand column in the prodlocation table (specified by cursor curs1) to the value of the expression (onhand - 100). The changes are made only in the row currently referenced by the cursor.
UPDATE prodlocation SET onhand = (onhand - 100) +
WHERE CURRENT OF curs1
The following example shows interactive data updating in an application file. The value of var1 is used in the expression that is assigned to the onhandcolumn of the prodlocation table. The UPDATE command changes values in onhand to the value of the expression (onhand - .var1) for all rows containing model numbers that begin with the letter C. The wildcard character % indicates one or more additional characters.
SET VARIABLE var1 TEXT
DIALOG 'Enter quantity by which to reduce inventory: ' var1 vend 1
SET VARIABLE var1 INTEGER
UPDATE prodlocation SET onhand = (onhand - .var1) +
WHERE model LIKE 'C%'
The following command changes the last names of two employees. This command omits the table name, thereby causing a global change to all tables that meet the WHERE clause criteria.
UPDATE SET emplname TO 'Smith-Simpson' WHERE +
(empfname = 'Mary' AND emplname = 'Simpson') OR +
(empfname = 'John' AND emplname = 'Smith')
The following example corrects a problem that can occur with an incorrect date sequence setting. For example, assume that you had the date sequence set to a four-digit year when you entered transactions, and you entered dates with a two-digit year (3/1/93). The dates would be stored as 3/1/0093. And, if you wanted the date to be in the 20th century, you could use the UPDATE command to modify the existing dates to 20th century dates by adding 1900 years to each date, with the ADDYR function.
The SET DATE command makes sure that you are using a four-digit year. The UPDATE command changes all transdate values to 20th century dates, where the current value of the column is less than 1/1/1900. The last SET DATE command returns to a two-digit date sequence and format.
SET DATE MM/DD/YYYY
UPDATE transmaster SET transdate = (ADDYR(transdate,1900)) +
WHERE transdate < 1/1/1900
SET DATE MM/DD/YY
Assume that you wanted to update the inventory table with the sum of the units sold from the orders table. Because there are many rows in the orders table for each part number, you cannot do this directly with the UPDATE command. The CREATE VIEW command creates a view containing the sum of the units sold from the orders table. The UPDATE command updates the inventory table by extracting the totalsold value from the view named orders_view for each part number.
CREATE VIEW orders_view (partid,totalsold) AS SELECT +
partid, sum(sold) FROM orders GROUP BY partid
UPDATE inventory SET onhand = (T1.onhand - T2.totalsold) +
FROM inventory T1, orders_view T2 +
WHERE T1.partid = T2.partid
