update

Update records in a table.

Not only can it update an in-memory table, it can also update a dfs table in which data isn't being written(a distributed table or a dimension table). 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
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;
// output
0.4999

update pt set x=x+1;

pt=loadTable("dfs://rangedb123", `pt)
select avg(x) from pt;
// output
1.4999