DB2 Version 9.7 for Linux, UNIX, and Windows

Deleting an array element (ARRAY_DELETE)

Deleting an element permanently from an array can be done using the ARRAY_DELETE function.

About this task

You would perform this task within SQL PL code in order to delete an element in an array.

Define a SET statement:
1. Declare and specify a variable that is of the same type as the array element.
2. Specify the assignment symbol, '='.
3. Specify the name of the ARRAY_DELETE function and within the required brackets, the name of the array, and the subindices that define the range of the elements to be deleted.
Execute the SET statement.

If the SET statement executes successfully, the array phones should contain the updated value.

For an array of phone numbers defined as:

phones		index		0		           1		           2		           3 				
         phone		'416-223-2233'	'416-933-9333'	'416-887-8887'	'416-722-7227'

After executing the following SQL statement:

SET phones = ARRAY_DELETE ( phones,  1, 2 );

The array, phones, will be defined as:

phones		index		0		           3 				
         phone		'416-223-2233'	'416-722-7227'

If the SET statement failed to execute successfully:

Verify the SQL statement syntax of the SET statement and execute the statement again.
Verify that the local variable is of the correct data type.
Verify that the array was created successfully and currently exists.