T.update(P:D)
Description:
Update records in a pseudo table.
Syntax:
T.update(P:D)
Note:
The function updates pseudo table T’s records according to table sequences/record sequences P and/or D; P should have same structure as T.
If a key value in P exists in pseudo table T, update the corresponding T’s record; if a key value in P doesn’t exist in T, append or insert the corresponding P’s record in T.
If a key value in D exists in pseudo table T, delete the corresponding T’s record; parameter D can be omitted.
Ignore parameter D when pseudo table T doesn’t have dimension. In this case, all records of P will be appended to the end of T.
Parameter:
|
T |
A pseudo table object |
|
P |
Data to be updated to T, which is a table sequence/record sequence having same structure as T; can be omitted |
|
D |
A table sequence/record sequence; delete records from T according to its key values; can be omitted |
Option:
|
@i |
Only append or insert non-matching records and ignore the matching ones |
|
@u |
Only update the matching records and ignore the non-matching ones |
|
@n |
Return updated, inserted and deleted records |
|
@w |
Only invalid when entity table T is stored column-wise; when P is a cursor having same order as its corresponding table sequence/record sequence; re-write fields of P to entity table T; does not supporting adding records to T |
|
@y |
Write the updated records to the memory instead of keeping it in the external memory, and perform concatenation on them during computation |
Return value:
Pseudo table object
Example:
|
|
A |
|
|
1 |
=create(file).record(["D:/file/pseudo/Employee.ctx"])
|
|
|
2 |
=pseudo(A1)
|
Generate a pseudo table object. |
|
3 |
=create(Dept,AvgSalary).record(["HR",7000,"CSD",6018.04]) |
Return a table sequence. |
|
4 |
=A2.update(A3) |
Use 3’s pseudo table to update A2’s pseudo table by matching their key values. |
|
5 |
=A4.import() |
|
Return the updated, inserted and deleted records:
|
|
A |
|
|
1 |
=create(file).record(["empD.ctx"]) |
Blow is data of composite table empD.ctx:
|
|
2 |
=pseudo(A1) |
Generate a pseudo table object. |
|
3 |
=create(EID,NAME).record([10,"bb",11,"cc"]) |
Return a table sequence:
|
|
4 |
=create(EID,NAME).record([1,"aaa",12,"ww"]) |
Return a table sequence:
|
|
5 |
=A2.update@n(A3:A4) |
Update pseudo table A2; when a table sequence A3’s key value exists in A2, update the corresponding record in the latter; otherwise, insert or append the corresponding record in A3 to A2. Delete a record in A2 when it matches an A4’s key value. @n option works to return the updated, inserted and deleted records.
|
|
6 |
=A2.import() |
Now import data of pseudo table A2 as follows:
|
When pseudo table T doesn’t have dimension:
|
|
A |
|
|
1 |
=file("empD.ctx") |
|
|
2 |
=A1.create@y(EID,NAME) |
Create a composite table. |
|
3 |
=connect("demo").cursor("select top 10 EID,NAME from employee") |
Return a cursor whose data is as follows:
|
|
4 |
=A2.append@i(A3) |
Append cursor A3’s data to pseudo table A2. |
|
5 |
=create(file).record(["empD.ctx"]) |
|
|
6 |
=pseudo(A5) |
Generate a pseudo table object. |
|
7 |
=create(EID,NAME).record([10,"bb",11,"cc"]) |
Generate a table sequence.
|
|
8 |
=create(EID,NAME).record([1,"Rebecca"]) |
Generate a table sequence.
|
|
9 |
=A6.update(A7:A8) |
As entity table A6 doesn’t have dimension, parameter D (A8) is ignored during the update and append all records of P (A7) to A6. |
|
10 |
=A9.import() |
Now import data of pseudo table A9 as follows:
|
Use @y option to write the update to the memory:
|
|
A |
|
|
1 |
=create(file).record(["em.ctx"]) |
Return a pseudo table definition record. |
|
2 |
=pseudo(A1) |
Generate a pseudo table definition object. |
|
3 |
=A2.import() |
Import data from the pseudo table.
|
|
4 |
=create(EID,NAME).record([1,"AAAA"]) |
Return a table sequence:
|
|
5 |
=A2.update@y(A4) |
Update table sequence A4’ s records to pseudo table A2; @y option enables writing the update to the memory. |
|
6 |
=A2.import() |
Import data from the pseudo table, where the update in A5 is contained:
|
|
7 |
>A2.close() |
Close the pseudo table. |
|
8 |
=pseudo(A1).import() |
Import data from pseudo table A1; as A5 uses @y option, the update isn’t written to the external memory and the function return same result as A3.
|
Use @w option:
|
|
A |
|
|
1 |
=connect("demo").cursor("select top 5 STOCKID,DATE,CLOSING from STOCKRECORDS where STOCKID = ? ","000062") |
Return a cursor. |
|
2 |
=file("STOCKRECORDS.ctx") |
|
|
3 |
=A2.create@y(#STOCKID,DATE,CLOSING) |
Create a composite table file consisting of STOCKID, DATE and CLOSING fields, where STOCKID is the key. |
|
4 |
=A3.append@i(A1) |
Append data in cursor A1 to the composite table. |
|
5 |
=create(file).record(["STOCKRECORDS.ctx"]) |
|
|
6 |
=pseudo@v(A5) |
Generate a pseudo table object; use @v option to enable pure table-based column-wise computation on the pseudo table whose data comes from a composite table. |
|
7 |
=A6.import() |
Import data from pseudo table A6:
|
|
8 |
=connect("demo").cursor("select top 7 STOCKID,DATE from STOCKRECORDS where STOCKID = ?","000792") |
Return a a cursor whose content is as follows:
|
|
9 |
=A6.update@w(A8) |
Update records of pseudo table A6; use @w option to rewrite fields specified by parameter P while no new records are added. |
|
10 |
=A9.import() |
Import the updated pseudo table records:
|
