Table sequences are universally used in esProc. During data analysis and computing, we often need to add records to a table sequence or delete them from it, or modify the data in it. This article will illustrate the modification and maintenance of the table sequences through operations of adding, deleting and modifying records, as well as of resetting a table sequence and adding fields to it. Meanwhile, it will introduce the different ways of handling these operations in a record sequence.
1. Add records
In the following cellset, append a record r1 to the table sequence in A1 and insert a record r2 at the second row. r1 is to set field values in order and r2 is to set field values by specifying field names. Finally, insert 3 records r3, r4 and r5 consecutively after the first row. Of the three records, ID field and NAME field have values while values of other fields are null:
| A | |
| 1 | =demo.query(“select * from STUDENTS”) |
| 2 | >A1.insert(0,111,”Jessica”,”F”,18) |
| 3 | >A1.insert(2,25:ID,”William”:NAME,20:AGE,”M”:GENDER) |
| 4 | >A1.insert(2:3,25+#:ID,”William”+string(#):NAME) |
The operations of adding records to the table sequence in A1 are performed successively in A2, A3 and A4. Click 
in the tool bar to execute the code step by step and you will see the changes of the original table sequence as follows:
As can be seen from the above code, T.insert() function is always used to add records in a table sequence. It can specify positions for the records to be inserted, or set fields for new values. If fields haven’t been set, their values are null.
Different from a table sequence, a record sequence is a sequence composed of references of the records. So you can only add new references of the records to a record sequence:
| A | |
| 1 | =demo.query(“select * from STUDENTS”) |
| 2 | =A1.dup() |
| 3 | >A1.insert(0,111,”Jessica”,”F”,18) |
| 4 | >A2.insert(2, A1(8)) |
The record sequence in A2 is created from the records of the table sequence in A1 using dup function. To create another table sequence by duplicating the original table sequence, you need to use T.dup@t() function. Both A1 and A2 and the table sequence in A1 in the previous example have the same data. The program in the cellset will still be executed step by step. After the code in A3 is executed, the data in A1 and A2 are respectively as follows:
It can be seen that a new record has been added to the table sequence, but the original records haven’t been modified. So the record sequence in A2 remains the same. A4 inserts the new record into the record sequence, but the position into which the record is inserted is not the corresponding one of the record’s position in the table sequence. When the code is executed, the record sequence in A2 becomes as follows:
There are a lot of differences between the syntax of table sequences and that of record sequences for adding records. The newly added records in a table sequence are physical ones, so you can use insert function directly in table sequence A1. But you can only add the references of records to a record sequence, so the physical records for referencing must exist first. In the example, the physical records are stored in A1. (Or they can be the records of other table sequences. But the records of the same record sequence should be of the same structure; otherwise the computed result could be wrong.)
That the first parameter of insert function is 0 means appending records at the end of the original records. If not, insert the records in the specified positions. The rule applies to both table sequences and records sequences.
2. Delete records
In the following cellset, first delete the second record, then delete the first and second records, and finally delete the records of students who are older than 15:
| A | |
| 1 | =demo.query(“select * from STUDENTS”) |
| 2 | >A1.delete(2) |
| 3 | >A1.delete([1,2]) |
| 4 | >A1.delete(A1.select(AGE>15)) |
The operations of deleting records from the table sequence in A1 are performed successively in A2, A3 and A4. After the code is executed step by step, you can see the changes of the table sequence in A1 as follows:
delete function is used to delete records from a table sequence. With the function, you can specify the sequence numbers, and a sequence composed of the sequence numbers; or you can delete all the records of the corresponding record sequence from the table sequence. The code in A4 can also be written as >A1.delete(A1.pselect@a(AGE>15)), which has the same effect.
Let’s look at the case in a record sequence:
| A | |
| 1 | =demo.query(“select * from STUDENTS”) |
| 2 | =A1.dup() |
| 3 | >A2.delete(2) |
| 4 | >A2.delete([1,2]) |
The record sequence in A2 and the table sequence in the previous example have the same original data. First A3 deletes a record, and then A4 deletes two records. After the code is executed step by step, you can see the changes of the record sequence in A2:
By checking the data in A1, you’ll find that the original data in the table sequence remain unchanged:
It is the physical records that have been deleted in the table sequence; while it is the references of the records that have been deleted in the record sequence, which won’t affect the original table sequence.
3. Modify records
In the following cellset, first modify the GENDER field into Female and the NAME field into Violet in the second record; and then add 10 to each ID of the three consecutive records after the first:
| A | |
| 1 | =demo.query(“select * from STUDENTS”) |
| 2 | >A1.modify(2, “Female”:GENDER, “Violet”:NAME) |
| 3 | >A1. modify (2:3,ID+10:ID) |
The original data of the table sequence in A1 are the same as those in the above example. A2 and A3 modify the table sequence. After the code is executed step by step, you can see the changes of the data in A1:
modify function is used to modify records in a table sequence. It is also used to modify data in a single record.
It is forbidden to modify records in a record sequence. The modification must be performed in the original table sequence.
You can also modify the field values of a record into those of another record according to their positions:
| A | |
| 1 | =demo.query(“select * from STUDENTS”) |
| 2 | >A1(5).modify@r(A1(1)) |
Record pasting is the operation of modifying records using modify@r function. After the record pasting operation in A2 is executed, the fifth record in the table sequenc has been modified:
The records being pasted can be from the same table sequence, or other table sequences. The record pasting is performed on the function of the records, not on table sequences, so it applies to record seqences. The operation can modify the original table sequence when executed. In esProc, P.paste(P‘) can be used to paste multiple records of a record sequence simultaneously.
4. Reset table sequences
Using reset function, you can empty a table sequence while retaining its data structure:
| A | |
| 1 | =demo.query(“select * from STUDENTS”) |
| 2 | >A1.reset() |
Resetting table sequences can remove useless records and release the memory:
5. Add fields
It is not allowed to add fields to a table sequence or delete them from it. To add new fields to a table sequence, such as computed columns, you can create a new table sequence using derive function:
| A | |
| 1 | =demo.query(“select * from STUDENTS”) |
| 2 | =A1.derive(left(NAME,1):INIT) |
The data are as follows after A2 adds new fields to the table sequence:
derive function can also be used in record sequences. No matter the computing object is a table sequence or a sequence, the function will always create a new table sequence. If all the original fields are not needed in the newly-created table sequence, new function can be used to create the table sequence. If the data in the original table sequence A1 are of no use, you’d better empty the cell value of A1, or reset the table sequence using reset function.