Difference between revisions of "SQL UPDATE"
From Lianjapedia
Helen.george (Talk | contribs) |
Yvonne.milne (Talk | contribs) |
||
Line 10: | Line 10: | ||
==See Also== | ==See Also== | ||
− | [[ALTER TABLE]], [[SQL CLOSE|CLOSE]], [[CREATE DATABASE]], [[CREATE TABLE | + | [[ALTER TABLE]], [[SQL CLOSE|CLOSE]], [[CREATE DATABASE]], [[CREATE TABLE]], [[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]] |
==Description== | ==Description== | ||
Line 18: | Line 18: | ||
!Keywords||Description | !Keywords||Description | ||
|- | |- | ||
− | |database||The name of the database to which the table belongs | + | |database||The name of the database to which the table belongs. 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 | + | |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. |
|- | |- | ||
|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 77: | Line 77: | ||
</code> | </code> | ||
− | |||
− | |||
[[Category:Documentation]] | [[Category:Documentation]] | ||
[[Category:Commands]] | [[Category:Commands]] | ||
[[Category:SQL|UPDATE]] | [[Category:SQL|UPDATE]] |
Revision as of 12:53, 19 December 2012
Contents
Purpose
Updates specified columns
Syntax
UPDATE [<database>!]<table>
SET <column> = <expr> [,...]
[WHERE <condition> | CURRENT OF <cursor>]]
See Also
ALTER TABLE, CLOSE, CREATE DATABASE, CREATE TABLE, 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. 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. |
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()