SQL UPDATE

UPDATE

Class

SQL Applications

Purpose

Updates specified columns

Syntax

UPDATE [<database>!] [FROM XML <xml filename> | SET <column> = <expr> [,...] [WHERE <condition> | CURRENT OF <cursor>]]

See Also

ALTER TABLE, CREATE TABLE, DB_DATADIR, EXECUTE IMMEDIATE, GETENV(), INSERT, PSEUDO COLUMNS, SELECT, SET TCACHE, SET XMLFORMAT,

Description

The UPDATE statement updates columns in the specified . To update data you must be the owner of the table or have already been granted UPDATE privileges.

Keywords	Description
database	The name of the database to which the table belongs. Databases in Recital are implemented as directories containing files that correspond to the and associated files in the database. Operating System file protection can be applied individually to the files for added security. The directory is a sub-directory of the Recital data directory. The environment variable / symbol DB_DATADIR points to the current Recital data directory and can be queried using the GETENV() function. Files from other directories can be added to the database using the ADD TABLE command or via the database catalog and SET AUTOCATALOG functionality. The '!' character must be included between the database name and the table name.
table	The name of the table on which to perform the update. When data is being updated in encrypted tables, the table reference can include the three-part encryption key, enclosed in angled brackets, appended to the table name. The SET ENCRYPTION command allows a default encryption key to be defined. If the key is not included in the <table>, this default key will be used. If the default key is not the correct key for the table, an error will be given. If no default key is active, a dialog box will be displayed in Recital Terminal Developer to allow the user to enter the key.
FROM XML <xml filename>	Specify an XML file to use as input for the UPDATE.
column	The name of a column of the table or view that is to be updated. If you omit a column from the table then the data will be unchanged.
expr	The new value to be assigned to the corresponding column. Date constants can be specified as valid dates in the current format (SET DATE, SET CENTURY, SET MARK) or as a character string in the format "DD-MMM-YYYY", e.g. "01-Sep-2002".
condition	Restricts the rows updated to those for which the specified condition is TRUE.
CURRENT OF	Updates only the row most recently fetched by the cursor.

Example

// Update all accounts that are now overdue by adding a 15% commission charge
EXEC SQL
UPDATE accounts
SET ord_value=ord_value*1.15, due_date = date()+30
WHERE paid_date < date();
 
// Declare the cursor to select records from the accounts table
EXEC SQL
DECLARE accounts CURSOR FOR
SELECT name, address, ord_value, balance
FROM accounts
WHERE ord_date < date(); 
 
// Open the cursor
EXEC SQL
OPEN accounts;
 
// Fetch records one at a time from the cursor and update them
EXEC SQL
FETCH accounts
INTO m_name, m_address, m_ord_value, m_balance;
DO WHILE sqlcode = 0
IF .not. empty(m_name) .and. m_balance <> 0
EXEC SQL
UPDATE accounts
SET ord_value = ord_value*1.15, due_date = date()+30
WHERE CURRENT OF accounts;
ENDIF
EXEC SQL
FETCH accounts INTO m_name, m_address, m_ord_value, m_balance;
ENDDO
 
// Close the cursor and free up any resources used for the cursor
EXEC SQL
CLOSE accounts;
EXEC SQL
DROP CURSOR accounts;
 
// Update an encrypted table
EXEC SQL
UPDATE encacc<key_1,key_2,key_3>
SET ord_value=ord_value*1.15, due_date = date()+30
WHERE paid_date < date();

Products

Recital Database Server, Recital Mirage Server, Recital Terminal Developer