update()

db.update(A:A',tbl,F_i:x_i,…;P,…)

Update a database table based on a sequence/record sequence

db.update(cs,tbl,F_i:x_i,…;P,…)

Update a database table based on a cursor

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; it is regarded as the same as Fi when omitted

P

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

@u

Compare primary keys between the database table and A' to generate an UPDATE statement; skip the comparison and directly update 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(A:A',tbl,Fi:xi,…;P,…)

Only perform delete operation and A' must be in place

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 update 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, update 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,USERID:#,USERNAME;USERID)

Ignore value of the auto-incrementing field 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

A composite table’s entity table

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

@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

When parameter P is a cursor having same order as the original entity table, update the latter’s fields that P has; only valid when T is stored in column-wise format and no records should be added

@y

Keep table sequences/record sequences P and D in the memory and perform the MERGE when computation begins without writing the updated result set to the external memory

A

1

=file("emp.ctx")

Generate a composite table file.

2

=A1.create@y(#EID,NAME)

Create the composite table’s base table.

3

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

Return a cursor.

4

=A2.append@i(A3)

Append data in A3’s cursor to A2’s base table.

5

=A2.attach(table3,SURNAME)

Add attached table table3 that has two fields – EID, which is the key, and SURNAME, to the base table.

6

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

Return a cursor.

7

=A5.append@i(A6)

Append data in A6’s cursor to A5’s pseudo table.

8

=A2.cursor().fetch()

View data in the base table:

9

=A5.cursor().fetch()

View data in the attached table:

10

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

Return a table sequence.

11

=A2.update(A10)

Update the base table using A10’s table sequence; update the corresponding base table record when a key value of table sequence A10 exists in the base table, and append the corresponding A10’s record to the base table when its key value doesn’t exist in the latter.

12

=A2.cursor().fetch()

View data in the base table again:

13

=create(EID,SURNAME).record([2,"aa",10,"bb"])

Return a table sequence.

14

=A5.update@in(A13)

With @i option, only handles insert and ignore records with same key values; the cooperation with @n option makes the function return only the inserted records.

15

=A5.cursor().fetch()

View data in the attached table again:

16

=create(EID,SURNAME).record([1,"ss",6,"cc"])

Return a table sequence.

17

=A5.update@un(A16)

With @u option, only handles update and ignore records with different key values; the cooperation with @n option makes the function return only the updated records.

18

=A5.cursor().fetch()

View data in the attached table:

A

1

=file("empD.ctx")

2

=A1.create@y(#EID,NAME)

Create a composite table using EID as the key

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 data of cursor A3 to entity table A2.

5

=create(EID,NAME).record([10,"bb",11,"cc"])

Generate a table sequence:

6

=create(EID,NAME).record([1,"Rebecca"])

Generate a table sequence:

7

=A2.update@n(A5:A6)

Update entity table A2; when a table sequence A5’s key value exists in A2, update the corresponding record in the latter; otherwise, insert the corresponding record in A5 to A2. Delete a record in A2 when it matches an A6’s key value. @n option works to return the updated, inserted and deleted records.

8

=A2.import()

Now import data from entity table A2 as follows:

A

1

=file("empPD.ctx")

2

=A1.create@y(#EID,NAME)

Create a composite table using EID as the key.

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 data in cursor A3 to entity table A2.

5

=create(EID,NAME).record([1,"bb",2,"cc"])

Generate a table sequence:

6

=create(EID,NAME).record([1,])

Generate a table sequence:

7

=A2.update@n(A5:A6)

Update entity table A2 - append A5’s records to A2 when their key values exist in A2 and when key values of records of A6 exist in A2, delete corresponding records from A2, and use @n option to return updated records, inserted records and deleted records:

8

=A2.import()

As both parameter P and parameter D are present and the function first handles P and then D, the record whose key value is 1 will be deleted in the end; below is the final data in entity table A2:

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 entity table A2.

5

=create(EID,NAME).record([10,"bb"])

Generate a table sequence:

6

=create(EID,NAME).record([1,"Rebecca"])

Generate a table sequence:

7

=A2.update(A5:A6)

As entity table A2 doesn’t have dimension, parameter D (A6) is ignored during the update and append all records of P (A5) to A2.

8

=A2.import()

Now import data from entity table A2 as follows:

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 entity table A2.

5

=create(EID,NAME).record([1,"Rebecca"])

Generate a table sequence:

6

=A2.update@y(:A5)

Update entity table A2 by deleting records that match A5’s key values; @y option works to keep the update result set in the memory and perform MERGE when computation really begins.

7

=A2.import()

Now import data from entity table A2 as follows:

8

=A2.close()

Close entity table A2.

9

=A1.open().import@x()

Re-open entity table A1 to retrieve data from it; the updated data in A6 has been restored:

A

1

=demo.cursor("select STOCKID,DATE,CLOSING from STOCKRECORDS  where STOCKID = ? and DATE<?","000062","2015-01-20")

2

=file("STOCKRECORDS.ctx")

Create a composite table file.

3

=A2.create@y(#STOCKID,DATE,CLOSING)

Create the composite table’s base table.

4

=A3.append@i(A1)

Append data of A1’s cursor to the composite table’s base table.

5

=A2.open().cursor()@x.fetch()

Fetch data from the composite table as follows:

6

=demo.cursor("select STOCKID,DATE  from STOCKRECORDS  where STOCKID = ?","000792")

7

=A2.open()

8

=A7.update@w(A6)

Update content of A6’s cursor to composite table STOCKRECORD.ctx, during which @w option is used to rewrite fields A6 has. The process is this: update A6’s STOCKID field and Date field into A7’s composite table and do not change A7’s CLOSING field while keeping the original number of records in A7 without adding any new record to it.

9

=A7.cursor()@x.fetch()

Fetch data from the updated composite table file as follows:

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

@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

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()

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:

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:

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.

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: