DELETE FROM SQL Statement

DELETE statements do not actually remove a record from a data file but do set the Record Marked Deleted Flag in the NCS Header to Y, for Yes.

The supported syntax is:

DELETE FROM <table> WHERE <search-exp>

Each document in an application defines both a table and a view. The table name is the name of the document, and the view name is "Simple View of " followed by the document name. In addition, each application contains a table called MASTER and a corresponding "Simple view of MASTER". A view name can be used anywhere a table name is. Column names are the fields defined for a document.

Only a single table can be specified.

The WHERE clause is always optional, and if not specified all rows are affected. Sub-queries are not supported in the WHERE clause, but arbitrary Boolean expressions can be used to select rows based on the values found in the row, as in "SELECT name, score from form1 where score > 20 and (grade = 6 or (grade = 5 and teacher = ‘williams’))".

You can use the document number instead of the document name. This is not as much an optimization as a convenience considering the document titles are typically verbose. Keep these rules in mind:

• Document number must be specified in decimal format like 1, 2, 5 and so on.
• Document number 0 maps to the Master document.
• Do not use leading zeros for the document number.
• Specifying an invalid document number will cause a SQL parse error.
• It is not usually a great practice to hardwire document numbers.

See also

SQL Statements Accepted by the Data Provider

SQL Best Practices