CODEWITHSUNDEEP
kdb+
user9627191
user9627191

Reputation:

KDB+ Merging multiple update statements

q)d:([] f1:`a`b` ;f2:```c; m1:`x``z;m2:``y`z)

f1 f2 m1 m2
-----------
a     x    
b        y 
   c  z  z

I want to update the f1 & m1 columns to f2 & m2 respectively if f1 & m1 have nulls; actually I want to merge these 2 queries to one update statement :

update f1:f2 from d where null f1
update m1:m2 from d where null m1`

Upvotes: 2

Views: 515

Answers (3)

Utsav
Utsav

Reputation: 5918

Fill can be used to update nulls: If you want to update table d in place, then you can use:
update f2^f1,m2^m1 from`d
or 

![`d;();0b;`f1`m1!((^;`f2;`f1);(^;`m2;`m1))]

If you want to display the output of update without updating original table, then:
update f2^f1,m2^m1 from d or

![d;();0b;`f1`m1!((^;`f2;`f1);(^;`m2;`m1))]

Upvotes: 0

Jonathon McMurray
Jonathon McMurray

Reputation: 2981

An alternative you might like to consider is fill, ^ which allows you to fill nulls in one list with items from another list (in this case, the lists are columns in the table) e.g.

q)d:([] f1:`a`b` ;f2:```c; m1:`x``z;m2:``y`z)
q)update f2^f1,m2^m1 from d
f1 f2 m1 m2
-----------
a     x
b     y  y
c  c  z  z

Upvotes: 5

nyi
nyi

Reputation: 3229

You can use Triadic vector conditional evaluation ?

?[vb;exprtrue;exprfalse]

The new query would be : 

q)update f1:?[null f1;f2;f1] , m1:?[null m1;m2;m1] from d
f1 f2 m1 m2
-----------
a     x    
b     y  y 
c  c  z  z

Upvotes: 1

Related Questions