Difference between revisions of "SQL UPDATE"
From Recital Documentation Wiki
Helengeorge (Talk | contribs) (→Class) |
Helengeorge (Talk | contribs) |
||
| Line 1: | Line 1: | ||
| − | |||
| − | |||
==Purpose== | ==Purpose== | ||
Updates specified columns | Updates specified columns | ||
| Line 90: | Line 88: | ||
==Products== | ==Products== | ||
| − | Recital | + | Recital Server, Recital |
[[Category:Documentation]] | [[Category:Documentation]] | ||
[[Category:Commands]] | [[Category:Commands]] | ||
[[Category:SQL|UPDATE]] | [[Category:SQL|UPDATE]] | ||
Revision as of 16:21, 25 November 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 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 Server, Recital
