Delete

Delete is used to delete one or several lines from a database table.

Syntax

Delete [ABV]Delete [ABV] KEY MODEDelete [ABV] KEY MODE KEY_VALUEDelete [ABV] MODEDelete [ABV] MODEK KEY_VALUEDeleteDelete KEY MODEDelete KEY MODEK KEY_VALUEDelete MODEDelete MODEK KEY_VALUEDelete [ABV] Where CONDITIONDelete Where CONDITION

• ABV is the abbreviation of a table. It must match a File declaration.
• KEY is the description of the key used to access the table. It can include one of the following syntaxes:
  • KEYVAR where KEYVAR is the code of a key declared in the dictionary or the name of a temporary index from an Order By Key clause.
  • KEYVAR(INDEX) where INDEX is a numeric expression giving the number of key parts for the condition. When omitted, all the key parts are used.
• KEY_VALUE is a list of expressions separated by semicolons.
• MODEK is one of the following operators: =, <, <=, >=, >.
• MODE can be MODEK or the Curr keywords.
• CONDITION is a logical condition based on columns in the table and constants or variable available in the context.

Examples

 # First example : let's read a record and delete it# When no argument is given, the mode is Curr, the table is the default oneLocal File CUSTOMER [CUST]Read [CUST]CODE="JOHNDOE"If fstat=0DeleteEndif# Second example : let's delete invoice lines# If no transaction is opened, a transaction is performed# Otherwise, the transaction is supposed to be done at the previous levelFunprog DELETE_INVOICE(INV_CODE)Value Char INV_CODE()Local Integer RETURN_STATUS, IF_TRANSACTIONLocal File INVOICE [INV], INVLINES [INL]IF_TRANSACTION=adxlog# Let's perform the header record deletionIf IF_TRANSACTION : Trbegin INVOICE, INVLINES : EndifDelete [INV]CODE=INV_CODERETURN_STATUS=fstat# Error handling : the only status that is a real error is when a locking problem occursIf RETURN_STATUS=[V]CST_ALOCKIf IF_TRANSACTION : Rollback : EndifEnd RETURN_STATUSElsif adxdlrec=0# No record deleted, so no line has to be deleted# This can be considered as an error or not (if yes, we could return [V]CST_AOUTSEARCH)If IF_TRANSACTION : Commit : EndifEnd 0Endif# Let's delete the linesDelete [INL]CODE(1)=INV_CODERETURN_STATUS=fstatIf RETURN_STATUS<>[V]CST_ALOCK : RETURN_STATUS=0 : Endif# Error handlingIf IF_TRANSACTIONIf RETURN_STATUSRollbackElseCommitEndifEndifEnd RETURN_STATUS# Third example : deletion of all the lines in a table in one transaction# If a lot of lines are there, it will be faster to destroy the table and to recreate itLocal File TEMPTABLE [TMP]Delete [TMP] Where KEY>= ""

Description and comments

Delete is used to delete lines from a table according to various modes listed below. Three cases can occur:

• With Curr mode: the current line is deleted.
• With modes <, <=, =, >= and >, all the lines corresponding to the selected read mode are deleted. If a Where clause has been specified in the File declaration or with an additional Filter instruction, the filtering is taken into account.
• With a Where clause, all the selected lines (including File and Filter defined Where) are deleted.

If no key value is specified for the modes that require one ( <, >, <=, >=, = ), the current value of the variables of class [F] corresponding to the file is used.

When no key is provided, the last key used to access the table is used. If no previous access was done since the File instruction, the key defined by Order By clause (the first key by default) is used.

When a Where clause is given, the database identifies by itself the most appropriate key to be used depending on the clause.

Delete does not modify the content of the variables of the corresponding class [F].

Delete sets the system variable adxdlrec that contains the number of deleted lines.
fstat contains the return status of the delete operation. Only the following values can be returned by fstat:

There is a small difference of behavior between the SQL server and Oracle when the 'Delete... Where' syntax is used. If the 'where' clause cannot be directly sent to the database and has to be executed by the engine, a deletion loop with locking option is done. If a locking problem is encountered:

• As Oracle locks the lines before starting the deletion, the locking status is returned by Oracle before any 'Delete' has been made.
• As SQL server locks the records during the deletion process, it is returned by the SQL server when one of the records to be deleted is only locked.

 Local File SALESORDER [ORD]Trbegin SALESORDER# STATUS can have a value from 1 to N# A local variable array gives, for every status, if the case is blocking or not# Such a syntax cannot be transmitted to the databaseDelete [ORD] Where [L]BLOCKING_CASE([ORD]STATUS)=0If fstat=[V]CST_ALOCK# If oracle, no line has been deleted# If SQL server, some lines can have been deleted# So if we Commit here, the state is unexpected here !!! Endif# A much better code would be the followingLocal Char CONDITION(250)If sum([L]BLOCKING_CASE)<>0CONDITION="find([ORD]STATUS"For I=1 to dim([L]BLOCKING_CASE)If [L]BLOCKING_CASE(I) : CONDITION+=","+num$(I) : EndifNext ICONDITION+=")=0"# CONDITION contains now an expression like : "find([ORD]STATUS,1,5,8)=0"Delete [ORD] Where evalue(CONDITION) : # This will send a "not in" clause with a list of constantsEndif

Associated errors

See also

File, Where, For, fstat, adxdlrec.