update()

Read(347) Label: update,

Here’s how to use update() function.

db.update()

Description:

Update database tables.

Syntax:

db.update(A:A',tbl,Fi:xi,…;P,…)

Update a database table based on a sequence/record sequence

db.update(cs,tbl,Fi:xi,…;P,…)

Update a database table based on a cursor

Note:

The function updates Fi field of the tbl table with the value of computing expression xi over the namesake field in sequence A. If both Fi and xi are omitted, use the namesake field of A and its values.

P, is the key of table tbl. First compare the database table tbl with A', the original sequence, according to the primary key if the latter parameter exists and generate a delete command to delete database records that exist in A' but don’t exist in A. When a primary key value exists in A but doesn’t exist in A', insert the corresponding record in A into the database table. If the primary key value exists in both A and A', compare the database record and the records in A and A' to see if there’s any change between the value of computing expression xi over Fi field in sequence A and the original Fi field value; and update the database table with the new value, if there is any.

When parameter A' is absent, the function supports only the insert and update operattions; no delete operation exists.

Parameters:

db

Database connection

A

A sequence /record sequence

cs

A cursor

A'

The original sequence/record sequence, whose data is considered consistent with the database table.

tbl

The name of a database table

Fi

A field in tbl

xi

An expression which will be computed based on A and its value will be the new value of Fi.

P

The key of tbl. If omitted, it will be retrieved from tbl; if the retrieval fails, then use A’s

Options:

@u

Compare primary keys between the database table and A' to generate an UPDATE statement; skip the comparison and directly upadate the database table with A if A' is absent.

@i

Compare primary keys between the database table with A'generate INSERT statement; skip the comparison and directly insert records into the database table with A if A' is absent.

@a

Clear the target table before the update is executed; empty the database table before executing db.update(A:A',tbl,Fi:xi,…;P,…).

@k

After the execution is completed, the transaction won't be committed. If this option is omitted, the transaction will be committed.

@1

The first field is an auto-increment field with no corresponding expression.

@d

Only perform delete operation and A' must be in place.

Return value:

  None

Example:

Transaction commits automatically after the update is completed

 

A

 

1

=demo.query("select * from EMPLOYEE").keys(EID)

 

2

=file("D:/employee1.txt").import@t()

3

>demo.update(A2:A1,EMPLOYEE,EID,NAME,SURNAME,GENDER,STATE,BIRTHDAY,HIREDATE,DEPT,SALARY:SALARY+10;EID)

EID is the key, which must be included in the update fields. A1 and A2 have the same field names. Compare the primary keys of the database table and those of A1 and A2 to delete records that are held by A1 but not held by A2; and to insert records that exist in A2 but don’t exist in A1. Finally compare the primary key values to upadate field values of A2’s database table by performing SALARY+10 on SALARY field of records whose field values have changed

4

>demo.update@u(A2:A1,EMPLOYEE,EID,NAME,SURNAME,GENDER,STATE,BIRTHDAY,HIREDATE,DEPT,SALARY:SALARY+10;EID)

With @u option, upadate only the field values of the records in A2 by performing SALARY+10 on SALARY field of records whose field values have changed

5

>demo.update@u(A2,EMPLOYEE,EID,NAME,SURNAME,GENDER,STATE,BIRTHDAY,HIREDATE,DEPT,SALARY:SALARY+10;EID)

Skip the comparison of primary key values and directly insert A2’s computed records to the database table if A' is absent

6

>demo.update@i(A2:A1,EMPLOYEE,EID,NAME,SURNAME,GENDER,STATE,BIRTHDAY,HIREDATE,DEPT,SALARY:SALARY+10;EID)

With @i option, only insert field values into records and perform SALARY+10 on SALARY field of all records

7

>demo.update@i(A2,EMPLOYEE,EID,NAME,SURNAME,GENDER,STATE,BIRTHDAY,HIREDATE,DEPT,SALARY:SALARY+10;EID)

Skip the comparison of primary key values and directly insert records into the database table with A if A' is absent

8

>demo.update@a(A2:A1,EMPLOYEE,EID,NAME,SURNAME,GENDER,STATE,BIRTHDAY,HIREDATE,DEPT,SALARY:SALARY+10;EID)

First empty the database table EMPLOYEE before executing db.update(A:A',tbl,Fi:xi,…;P,…).

Perform operation on records of the resulting database table whose EID is 512

9

=file("D:/data_user.txt").import@t()

10

=mysql.query("select * from user_test").keys(userid)

userid, which is the key of user test table in MySQL database, is a auto-incrementing field.

11

>mysql.update@1(A9:A10,user_test,USERNAME)

There’s no need for an  auto-incrementing field to be written down as an expression

12

>demo.update@k(A2:A1,EMPLOYEE,EID,NAME,SURNAME,GENDER,STATE,BIRTHDAY,HIREDATE,DEPT,SALARY:SALARY+10;EID)

Do not commit a transaction after the execution

13

>demo.update@d(A2:A1,EMPLOYEE,EID,NAME,SURNAME,GENDER,STATE,BIRTHDAY,HIREDATE,DEPT,SALARY:SALARY+10;EID)

Compare key values to delete records that exist in A1 but don’t exist in A2; perform operation on records of the updated database table whose EID is 1, 2 and 3

14

=file("D://data.txt").cursor@t()

 

15

>demo.update(A14,EMPLOYEE,EID,NAME,SURNAME,GENDER,STATE,BIRTHDAY,HIREDATE,DEPT,SALARY:SALARY+10;EID)

A14 is a cursor

T .update( P )

Description:

Update records in an entity table.

Syntax:

T.update(P)

 

Note:

The function updates entity table T’s records by matching keys in T and record sequence P; P and T should have same structure. If a key value in P doesn’t exist in T, append or insert the corresponding record in T. Will auto-update T’s index, if there is one.

Parameters:

T

A composite table’s entity table

P

A table sequence/record sequence having same structure as T

Options:

@i

Update the records that can be inserted and ignore those whose values exist in the memory table

@u

Update the records that can be modified and ignore those whose values don’t exist in the memory table

@n

Return only the records that are updated and inserted

Return value:

  An entity table

Example: 

 

A

 

1

=file("D:\\emp8.ctx")

 

2

=A1.create(#EID,NAME;EID)

Create a composite table’s base table where EID field is the key by which records are segmented

3

=demo.cursor("select EID,NAME from employee where EID<20")

 

4

=A2.append(A3)

 

5

=A2.attach(table3,SURNAME)

Add entity table table3 to the base table

6

=demo.cursor("select EID,SURNAME from employee where EID< 5")

 

7

=A5.append(A6)

 

8

=create(EID,NAME).record([1,"A",10,"B"])

Create a table sequence

9

=A2.update(A8)

In the base table, update the record where EID is 1 and append to it a record where EID is 10

10

=create(EID,SURNAME).record([2,"a",10,"b"])

Create a table sequence

11

=A5.update@i(A10)

In entity table table3, insert a record where EID is 10 but won’t update the record where EID is 2

 

T .update( P )

Description:

Update records in a memory table.

Syntax:

T.update(P)

 

Note:

The function updates memory table T’s records by matching keys in T and record sequence P; P and T should have same structure. If a key value in P doesn’t exist in T, append or insert the corresponding record in T, in which the primary key always maintains its order. Will auto-update T’s index, if there is one.

Parameters:

T

A memory table

P

A table sequence/record sequence having same structure as T

Options:

@i

Only support insertion; ignore matching records

@u

Only support update; ignore mismatching records

@n

Return updated and inserted records

Return value:

A memory table

Example:

 

A

 

1

=demo.cursor("select EID,NAME,GENDER from employee where EID< 10")

Return a cursor

2

=A1.memory()

Return a memory table

3

=A2.keys(EID)

Set EID as the memory table’s primary key

4

=create(EID,NAME,GENDER).record([1,"A","AA",10,"B","BB"])

Return a table sequence

5

=A2.update@u(A4)

Update only

6

=A2.update(A4)

Comment A5 before performing this execution; below is the result:

Handle updating and append a record

7

=A2.update@i(A4)

Comment A5 and A6 before performing this execution; below is the result:

Insert only

8

=A2.update@n(A4)

Comment A5, A6 and A7 before performing this execution; below is the result:

Return updated and inserted records

 

T.update(P)

Description:

Update records in a cluster composite table.

Syntax:

T.update(P)

 

Note:

The function updates cluster composite table T’s records by matching keys in T and record sequence P; P and T should have same structure. If a key value in P doesn’t exist in T, append or insert the corresponding record in T. Will auto-update T’s index, if there is one. Suppport only the permanent distributed files and the update can happen to the patition in any node.

Parameters:

T

A cluster composite table

P

A table sequence/record sequence of the same structure as T

Example:

 

A

 

1

=file@0@z("emp10.ctx",["192.168.0.118:8281"])

 

2

=A1.create()

Open the distributed cluster composite table

3

=create(EID,NAME).record([1,"A","F",10,"B","M"])

Create a table sequence

 

4

=A2.update(A3)

Update the record where EID=1 and append a record where EID=10