Kbase P36936: How to insert or change data into array fields in SQL-92
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
04/02/2011 |
|
Status: Verified
GOAL:
How to insert or change data into array fields in SQL-92
GOAL:
How to update extent fields in OpenEdge SQL
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.x
OpenEdge 10.x
FIX:
Use the standard INSERT syntax to insert the same value to each element of an array field:
INSERT INTO table_name ( array_column_name ) VALUES ( 'one_value' )
or use it to insert a specific (and complete, cannot be used partially) chain of values for the array field:
INSERT INTO table_name ( array_column_name) VALUES ('value-1;value-2;...;value-n')
To change the first element of the CHARACTER data array field, array_column_name:
UPDATE
some_table_name
SET
array_column_name = PROARR_ESCAPE('aaa') || ';' || PRO_ELEMENT(array_column_name,2,3)
WHERE
some_field_name = 9
To UPDATE numeric data array fields use syntax as per the following two examples against the SalesRep table of the Sports2000 database:
1. Modify the first month sales quota for salesrep 'JAL':
UPDATE
PUB.SalesRep
SET
MonthQuota = '1000' || ';' || PRO_ELEMENT(MonthQuota, 2, 12)
WHERE
SalesRep = 'JAL';
2. Modify the first and fifth month sales quota for salesrep 'JAL':
UPDATE
PUB.SalesRep
SET
MonthQuota = '1111' || ';' || PRO_ELEMENT(MonthQuota, 2, 4) || ';' || '5555' || ';' || PRO_ELEMENT(MonthQuota, 6,12)
WHERE
SalesRep = 'JAL';