Index...
Centrallix Documentation
|
6.12 DELETE Statements
DELETE Statements
DELETE statements are used to remove existing rows from a table or data source. In Centrallix, these statements mostly follow the usual convention.
A DELETE statement contains a list of tables to include in the query, with a possible WHERE clause to provide criteria on what to delete. Deletions always only occur on one table, regardless of how many tables are involved in the query (see Joins, below).
Here are two simple examples which delete all rows in a table:
DELETE /data/Items.csv/rows
DELETE FROM /data/Items.csv/rows
DELETE statements may also contain a WHERE clause to restrict which records are deleted, as shown below:
DELETE /data/Items.csv/rows
WHERE :id = 11528
Joins and Subtrees in DELETE statements
In the same way that a FROM clause may contain the SUBTREE and IDENTITY keywords, a DELETE statement may also use those keywords. In fact, if the DELETE statement has more than one data source (i.e., if it contains a join), and the table to have rows deleted is not the first table in the list, then the IDENTITY keyword must be used.
The SUBTREE keyword may also be used, but since a HAVING clause may not be used with a DELETE query, its usefulness is limited.
Here is an example of using the SUBTREE keyword to delete 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):
DELETE FROM INCLUSIVE SUBTREE /data/Accounts.qyt/1000
Note that in the above case, since the INCLUSIVE keyword is used, the object /data/Accounts.qyt/1000 itself is deleted along with all of its descendents.
Here is an example of an DELETE using a join. Remember the need to use the IDENTITY keyword if the data source where deletions are to occur is not listed first.
DELETE FROM /database/Customer/rows c,
IDENTITY /database/Address/rows a
WHERE :c:do_not_mail = 1
AND :c:cust_id = :a:cust_id
Centrallix is flexible on the exact syntax of the DELETE clause, so the following forms for a multi-source DELETE would achieve the same effect:
DELETE /database/Address/rows a
FROM /database/Customer/rows c
WHERE :c:do_not_mail = 1
AND :c:cust_id = :a:cust_id
DELETE FROM /database/Address/rows a
FROM /database/Customer/rows c
WHERE :c:do_not_mail = 1
AND :c:cust_id = :a:cust_id
Comments...
(none yet)
Add a Comment...
|
