SQL UPDATE

From Recital Documentation Wiki
Revision as of 14:08, 22 August 2014 by Yvonnemilne (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

Purpose

Updates specified columns


Syntax

UPDATE [<database>!]<table>

SET <column> = <expr> [,...]

[WHERE <condition> | WHERE CURRENT OF <cursor>]


See Also

ALTER TABLE, CLOSE, CREATE DATABASE, CREATE TABLE, DB_DATADIR, DECLARE CURSOR, DELETE, DROP CURSOR, EXECUTE IMMEDIATE, FETCH, GETENV(), INSERT, OPEN, OPEN DATABASE, PSEUDO COLUMNS, SELECT, 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.
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()
 
// Example using a cursor
OPEN DATABASE southwind
 
// Declare the cursor to select records from the orders table
DECLARE cursor1;
  CURSOR FOR;
  SELECT orderid, customerid, orderdate;
  FROM orders;
  WHERE requireddate < date()
 
// Open the cursor
OPEN cursor1
 
// Fetch records one at a time from the cursor and update them
FETCH cursor1;
  INTO m_order, m_customer, m_orderdate
  do while sqlcode = 0
    if not empty(m_order) and empty(shippeddate)
        UPDATE orders;
          SET shippeddate = date();
          WHERE CURRENT OF cursor1
    endif
    FETCH cursor1;
      INTO m_order, m_customer, m_orderdate
enddo
 
// Close the cursor and free up any resources used for the cursor
CLOSE cursor1
DROP CURSOR cursor1
 
CLOSE DATABASES
 
// Example with 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