Difference between revisions of "SQL UPDATE"
From Recital Documentation Wiki
Helengeorge (Talk | contribs) |
Yvonnemilne (Talk | contribs) |
||
| Line 42: | Line 42: | ||
<code lang="recital"> | <code lang="recital"> | ||
// Update all accounts that are now overdue by adding a 15% commission charge | // Update all accounts that are now overdue by adding a 15% commission charge | ||
| − | + | ||
| − | UPDATE accounts | + | UPDATE accounts; |
| − | SET ord_value=ord_value*1.15, due_date = date()+30 | + | SET ord_value=ord_value*1.15, due_date = date()+30; |
| − | WHERE paid_date < date() | + | WHERE paid_date < date() |
// Declare the cursor to select records from the accounts table | // Declare the cursor to select records from the accounts table | ||
| − | + | DECLARE accounts; | |
| − | DECLARE accounts CURSOR FOR | + | CURSOR FOR; |
| − | SELECT name, address, ord_value, balance | + | SELECT name, address, ord_value, balance; |
| − | FROM accounts | + | FROM accounts; |
| − | WHERE ord_date < date() | + | WHERE ord_date < date() |
// Open the cursor | // Open the cursor | ||
| − | + | OPEN accounts | |
| − | OPEN accounts | + | |
// Fetch records one at a time from the cursor and update them | // Fetch records one at a time from the cursor and update them | ||
| − | + | FETCH accounts; | |
| − | FETCH accounts | + | INTO m_name, m_address, m_ord_value, m_balance |
| − | INTO m_name, m_address, m_ord_value, m_balance | + | do while sqlcode = 0 |
| − | + | if not empty(m_name) and m_balance <> 0 | |
| − | + | UPDATE accounts; | |
| − | + | SET ord_value = ord_value*1.15, due_date = date()+30; | |
| − | UPDATE accounts | + | WHERE CURRENT OF accounts |
| − | SET ord_value = ord_value*1.15, due_date = date()+30 | + | endif |
| − | WHERE CURRENT OF accounts | + | FETCH accounts INTO m_name, m_address, m_ord_value, m_balance |
| − | + | enddo | |
| − | + | ||
| − | FETCH accounts INTO m_name, m_address, m_ord_value, m_balance | + | |
| − | + | ||
// Close the cursor and free up any resources used for the cursor | // Close the cursor and free up any resources used for the cursor | ||
| − | + | CLOSE accounts | |
| − | CLOSE accounts | + | DROP CURSOR accounts |
| − | + | ||
| − | DROP CURSOR accounts | + | |
// Update an encrypted table | // Update an encrypted table | ||
| − | + | UPDATE encacc<key_1,key_2,key_3>; | |
| − | UPDATE encacc<key_1,key_2,key_3> | + | SET ord_value=ord_value*1.15, due_date = date()+30; |
| − | SET ord_value=ord_value*1.15, due_date = date()+30 | + | WHERE paid_date < date() |
| − | WHERE paid_date < date() | + | |
</code> | </code> | ||
Revision as of 15:22, 22 December 2009
Purpose
Updates specified columns
Syntax
UPDATE [<database>!]<table>
[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 <table>. 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 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 DECLARE accounts; CURSOR FOR; SELECT name, address, ord_value, balance; FROM accounts; WHERE ord_date < date() // Open the cursor OPEN accounts // Fetch records one at a time from the cursor and update them 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 UPDATE accounts; SET ord_value = ord_value*1.15, due_date = date()+30; WHERE CURRENT OF accounts endif 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 CLOSE accounts DROP CURSOR accounts // Update an encrypted table 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 Server, Recital
