Index...
Centrallix Documentation
|
6.11 UPDATE Statements
UPDATE Statements
UPDATE statements are used to modify existing rows in a table or data source. In Centrallix, these statements mostly follow the usual convention, with some changes and extra features to match the nature of Centrallix's data sources and other SQL commands.
Unlike INSERT statements which use the INSERT INTO ... SELECT form, UPDATE statements use normal attribute notation (using a colon) for the left hand side of assignment expressions.
Here is a simple example which updates all rows in a table:
UPDATE /data/Items.csv/rows
SET :process_date = getdate()
UPDATE statements may also contain a WHERE clause to restrict which records are updated, as shown below:
UPDATE /data/Items.csv/rows
SET :item_cnt = :item_cnt + 1
WHERE :id = 11528
Joins and Subtrees in UPDATE statements
In the same way that a FROM clause may contain the SUBTREE and IDENTITY keywords, an UPDATE statement may also use those keywords. In fact, if the UPDATE statement has more than one data source (i.e., if it contains a join), the keyword IDENTITY must be used on at least one of the data sources.
Here is an example of using the SUBTREE keyword to change a value on all objects in a given branch of a querytree (.qyt) object, which is probably in this case representing data from a hierarchical table (containing a key -> parent_key self-join):
UPDATE SUBTREE /data/Accounts.qyt/1000
SET :acct_type = "Asset"
Note that in the above case, the object /data/Accounts.qyt/1000 itself is not updated, but rather all descendents are (anything any level within the /data/Accounts.qyt/1000 "directory").
Here is an example of an UPDATE through a join. Remember the need to use the IDENTITY keyword.
UPDATE /database/Customer/rows c,
IDENTITY /database/Address/rows a
SET :a:is_obsolete = 1
WHERE :c:do_not_mail = 1
AND :c:cust_id = :a:cust_id
Updating Through Expressions
(note: as of the time of writing, updating through the substring() function was not yet working)
With SELECT queries, Centrallix supports updating computed fields in many cases. This capability carries through to UPDATE statements as well, allowing the use of a more complex expression on the left-hand side of each assignment in the UPDATE.
Here is an example of updating just three characters in a string field:
UPDATE /database/Customer/rows
SET substring(:cust_code, 1, 3) = 'VEN'
WHERE substring(:cust_code, 1, 3) = 'VDR'
Note that there are many cases where updating computed expressions does not make sense and so does not work.
Comments...
(none yet)
Add a Comment...
|
