Difference between revisions of "SQL UPDATE"

From Recital Documentation Wiki
Jump to: navigation, search
 
(7 intermediate revisions by 2 users not shown)
Line 1: Line 1:
−
==Class==
 
−
SQL Applications
 
−
 
−
 
 
==Purpose==
 
==Purpose==
 
Updates specified columns
 
Updates specified columns
Line 10: Line 6:
 
UPDATE [<database>!]&#060;table&#062;
 
UPDATE [<database>!]&#060;table&#062;
  
−
[FROM XML <xml filename> | SET <column> = <expr> [,...]
+
SET <column> = <expr> [,...]
  
−
[WHERE <condition> | CURRENT OF <cursor>]]
+
[WHERE <condition> | WHERE CURRENT OF <cursor>]
  
  
 
==See Also==
 
==See Also==
−
[[ALTER TABLE]], [[CREATE TABLE]], [[DB_DATADIR]], [[EXECUTE IMMEDIATE]], [[GETENV()]], [[SQL INSERT|INSERT]], [[SQL Pseudo Columns|PSEUDO COLUMNS]], [[SQL SELECT|SELECT]], [[SET TCACHE]], [[SET XMLFORMAT]],
+
[[ALTER TABLE]], [[SQL CLOSE|CLOSE]], [[CREATE DATABASE]], [[CREATE TABLE]], [[DB_DATADIR]], [[DECLARE CURSOR]], [[SQL DELETE|DELETE]], [[DROP CURSOR]], [[EXECUTE IMMEDIATE]], [[FETCH]], [[GETENV()]], [[SQL INSERT|INSERT]], [[OPEN]], [[OPEN DATABASE]], [[SQL Pseudo Columns|PSEUDO COLUMNS]], [[SQL SELECT|SELECT]], [[SET XMLFORMAT]]  
  
  
Line 29: Line 25:
 
|-
 
|-
 
|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 &#060;table&#062;, 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.
 
|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 &#060;table&#062;, 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.
 
|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.
Line 46: Line 40:
 
<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
−
EXEC SQL
+
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
+
// Example using a cursor
−
EXEC SQL
+
OPEN DATABASE southwind
−
DECLARE accounts CURSOR FOR
+
 
−
SELECT name, address, ord_value, balance
+
// Declare the cursor to select records from the orders table
−
FROM accounts
+
DECLARE cursor1;
−
WHERE ord_date < date();
+
  CURSOR FOR;
 +
  SELECT orderid, customerid, orderdate;
 +
  FROM orders;
 +
  WHERE requireddate < date()
  
 
// Open the cursor
 
// Open the cursor
−
EXEC SQL
+
OPEN cursor1
−
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
−
EXEC SQL
+
FETCH cursor1;
−
FETCH accounts
+
  INTO m_order, m_customer, m_orderdate
−
INTO m_name, m_address, m_ord_value, m_balance;
+
  do while sqlcode = 0
−
DO WHILE sqlcode = 0
+
    if not empty(m_order) and empty(shippeddate)
−
IF .not. empty(m_name) .and. m_balance <> 0
+
        UPDATE orders;
−
EXEC SQL
+
          SET shippeddate = date();
−
UPDATE accounts
+
          WHERE CURRENT OF cursor1
−
SET ord_value = ord_value*1.15, due_date = date()+30
+
    endif
−
WHERE CURRENT OF accounts;
+
    FETCH cursor1;
−
ENDIF
+
      INTO m_order, m_customer, m_orderdate
−
EXEC SQL
+
enddo
−
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 the cursor and free up any resources used for the cursor
−
EXEC SQL
+
CLOSE cursor1
−
CLOSE accounts;
+
DROP CURSOR cursor1
−
EXEC SQL
+
 
−
DROP CURSOR accounts;
+
CLOSE DATABASES
  
−
// Update an encrypted table
+
// Example with an encrypted table
−
EXEC SQL
+
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>
  
  
 
==Products==
 
==Products==
−
Recital Database Server, Recital Mirage Server, Recital Terminal Developer
+
Recital Server, Recital  
 
[[Category:Documentation]]
 
[[Category:Documentation]]
 
[[Category:Commands]]
 
[[Category:Commands]]
 
[[Category:SQL|UPDATE]]
 
[[Category:SQL|UPDATE]]

Latest revision as of 14:08, 22 August 2014

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

Retrieved from "https://www.recitalsoftware.com/wiki/index.php?title=SQL_UPDATE&oldid=16140"