CODEWITHSUNDEEP
typeskdb+
Riley Hun
Riley Hun

Reputation: 2785

KDB+: How to Update a Value from Table with Another Value of Different Type

The meta of the table relevantOverrides is as follows: 

security j
startDate d
endDate d
dataItem s
val f

I have an update query: 

 relevantOverrides:update val:163390j from relevantOverrides where security = 18767

The val column has a float type, but I need to insert the updated value as an integer.

Upvotes: 0

Views: 1814

Answers (1)

Mark Kelly
Mark Kelly

Reputation: 1780

Since the list is of an atomic type, a number of a different type cannot be added in place easily.

One way of doing what you want is to append the generic null (::) or a value of the type you want to update (in this case a long) which will turn your list into a mixed list.

You can then update the values at the indices specified by the where clause before removing the generic null from the list and updating.

q)t
security startDate  endDate    dataItem val
-------------------------------------------------
2        2011.11.15 2003.11.17 dol      2.310108
8        2015.03.27 2015.01.12 nca      0.8724017
6        2012.06.15 2015.05.03 dfp      1.024432
9        2012.04.15 2001.07.18 hon      8.671096
0        2000.09.02 2009.12.29 ofm      7.278528

q)update val:-1_ @[val,(::);where security=6;:;163390j] from t
security startDate  endDate    dataItem val
------------------------------------------------
2        2011.11.15 2003.11.17 dol      2.310108
8        2015.03.27 2015.01.12 nca      163390
6        2012.06.15 2015.05.03 dfp      1.024432
9        2012.04.15 2001.07.18 hon      8.671096
0        2000.09.02 2009.12.29 ofm      7.278528

Although it is possible, you may run into difficulties when it comes operating on the table:

  • Firstly you will not be able to save the resultant table to disk as anything other than a flat table without running into issues

  • If you are able to get around this, you will have a lower query performance due to the column not being a vector.

  • The update function itself will be expensive as the column needs to be ammended.

Upvotes: 6

Related Questions