update
Update records in a table.
Not only can it update an in-memory table, since version 1.30.5, it can also update a DFS table or a dimension table in which data isn’t being written. Please note that if the data in a DFS table needs to be updated, the whole partition which includes the data will be updated; If the data in a dimension table needs to be updated, the whole table will be updated. Therefore, the operation to update a DFS table is only suitable for low-frequency update tasks such as minute-level update tasks, and not suitable for high-frequency update tasks, such as millisecond-level update tasks.
The update takes a multi-version approach and supports transactions. The system will create a new version to store the new data. Before committing the transaction, other SQL statements still can access the data in the old version. If data in multiple partitions needs to be updated, as long as the update on one partitions fails, the system will roll back all modifications on each partition.
Please note that the update clause cannot change the data type of a column.
Syntax
update
[table_name]
set col1=X1, [col2=X2,...]
[from table_joiner(table_names)]
[where condition(s)]
[context by col_name(s)]
Examples
Example 1: Update the in-memory table
sym = `C`MS`MS`MS`IBM`IBM`C`C`C$SYMBOL
price= 49.6 29.46 29.52 30.02 174.97 175.23 50.76 50.32 51.29
qty = 2200 1900 2100 3200 6800 5400 1300 2500 8800
timestamp = [09:34:07,09:36:42,09:36:51,09:36:59,09:32:47,09:35:26,09:34:16,09:34:26,09:38:12]
t1 = table(timestamp, sym, qty, price);
t1;
timestamp |
sym |
qty |
price |
|---|---|---|---|
09:34:07 |
C |
2200 |
49.6 |
09:36:42 |
MS |
1900 |
29.46 |
09:36:51 |
MS |
2100 |
29.52 |
09:36:59 |
MS |
3200 |
30.02 |
09:32:47 |
IBM |
6800 |
174.97 |
09:35:26 |
IBM |
5400 |
175.23 |
09:34:16 |
C |
1300 |
50.76 |
09:34:26 |
C |
2500 |
50.32 |
09:38:12 |
C |
8800 |
51.29 |
Add a column vol whose values are all NULL:
update t1 set vol=long();
t1;
timestamp |
sym |
qty |
price |
vol |
|---|---|---|---|---|
09:34:07 |
C |
2200 |
49.6 |
|
09:36:42 |
MS |
1900 |
29.46 |
|
09:36:51 |
MS |
2100 |
29.52 |
|
09:36:59 |
MS |
3200 |
30.02 |
|
09:32:47 |
IBM |
6800 |
174.97 |
|
09:35:26 |
IBM |
5400 |
175.23 |
|
09:34:16 |
C |
1300 |
50.76 |
|
09:34:26 |
C |
2500 |
50.32 |
|
09:38:12 |
C |
8800 |
51.29 |
$ t1.drop!(`vol);
Update the table trades by adding 0.5 to the price column and subtracting 50 from the qty column for records with stock symbol C.
update t1 set price=price+0.5, qty=qty-50 where sym=`C;
t1;
timestamp |
sym |
qty |
price |
|---|---|---|---|
09:34:07 |
C |
2150 |
50.1 |
09:36:42 |
MS |
1900 |
29.46 |
09:36:51 |
MS |
2100 |
29.52 |
09:36:59 |
MS |
3200 |
30.02 |
09:32:47 |
IBM |
6800 |
174.97 |
09:35:26 |
IBM |
5400 |
175.23 |
09:34:16 |
C |
1250 |
51.26 |
09:34:26 |
C |
2450 |
50.82 |
09:38:12 |
C |
8750 |
51.79 |
update t1 set price=price-0.5, qty=qty+50 where sym=`C;
context by can make group adjustments; whereas contextby can’t. The example below first calculates the average price for each stock with context by, then deducts the average price from each record’s original price.
update t1 set price=price-avg(price) context by sym;
t1;
timestamp |
sym |
qty |
price |
|---|---|---|---|
09:34:07 |
C |
2150 |
-0.8925 |
09:36:42 |
MS |
1900 |
-0.206667 |
09:36:51 |
MS |
2100 |
-0.146667 |
09:36:59 |
MS |
3200 |
0.353333 |
09:32:47 |
IBM |
6800 |
-0.13 |
09:35:26 |
IBM |
5400 |
0.13 |
09:34:16 |
C |
1250 |
0.2675 |
09:34:26 |
C |
2450 |
-0.1725 |
09:38:12 |
C |
8750 |
0.7975 |
Updating a table with table joins:
$ item = table(1..10 as id, 10+rand(100,10) as qty, 1.0+rand(10.0,10) as price)
$ promotion = table(1..10 as id, rand(0b 1b, 10) as flag, 0.5+rand(0.4,10) as discount);
$ item;
id |
qty |
price |
|---|---|---|
1 |
23 |
7.839664 |
2 |
44 |
7.635988 |
3 |
76 |
5.378054 |
4 |
91 |
8.078173 |
5 |
11 |
10.316152 |
6 |
58 |
9.510634 |
7 |
90 |
1.643082 |
8 |
68 |
5.787797 |
9 |
52 |
7.53352 |
10 |
62 |
6.222249 |
$ promotion;
id |
flag |
discount |
|---|---|---|
1 |
0 |
0.650346 |
2 |
0 |
0.697081 |
3 |
0 |
0.774207 |
4 |
1 |
0.819562 |
5 |
0 |
0.710393 |
6 |
0 |
0.728223 |
7 |
1 |
0.602512 |
8 |
0 |
0.71226 |
9 |
1 |
0.606631 |
10 |
0 |
0.765697 |
update item set price = price*discount from ej(item, promotion, `id) where flag=1;
item;
id |
qty |
price |
|---|---|---|
1 |
23 |
7.839664 |
2 |
44 |
7.635988 |
3 |
76 |
5.378054 |
4 |
91 |
6.620566 |
5 |
11 |
10.316152 |
6 |
58 |
9.510634 |
7 |
90 |
0.989976 |
8 |
68 |
5.787797 |
9 |
52 |
4.570069 |
10 |
62 |
6.222249 |
Example 2: Update the dfs table
login(`admin, `123456)
n=1000000
ID=rand(10, n)
x=rand(1.0, n)
t=table(ID, x)
db=database("dfs://rangedb123", RANGE, 0 5 10)
pt=db.createPartitionedTable(t, `pt, `ID)
pt.append!(t)
$ pt=loadTable("dfs://rangedb123", `pt)
$ select avg(x) from pt;
0.4999
$ update pt set x=x+1;
$ pt=loadTable("dfs://rangedb123", `pt)
$ select avg(x) from pt;
1.4999