Upsert multiple records to splayed table

Community Support
1

Hello everyone,

I have a splayed table set up and can append a single record to it:

``:hdb/progress/ set ( method:sym?ONETWOTWO; distance:1552 1623 1223):hdb/progress/ upsert (sym?THREE;1111)

However I’m having difficulties upserting multiple records at once. The docs say using rows instead of columns is preferred:

q)t upsert ((Prefect; 126); (Marvin; 200))

And it really works on in-memory table, but doesn’t on a splayed table. In fact, I’ve tried multiple combinations, e.g.

``:hdb/progress/ upsert ((sym?TWO;1678); (sym?THREE;999)) / version that works on in-memory table:hdb/progress/ upsert (sym?TWOTHREE;1678 999) / mimicking the batch insert:hdb/progress/ upsert flip (sym?TWOTHREE;1678 999) / the previous, only flipping to upsert by row`

And none of them work, yielding type error:

'type [2] ............................................/q/persistence_test.q:18: :hdb/progress/ upsert (sym?TWOTHREE;1678 999) ^ [0] (<load>) )

Please, what am I missing?

In the end I found out that upserting a new labeled table works

``:hdb/progress/ upsert (method:sym?TWOTHREE; distance:1678 999)

… but is there a version where I don’t have to provide the whole table (more specifically column names) and I append columns/rows just as for in-memory version?

Thank you!

Radek

2

There was a related discussion some time ago: https://groups.google.com/forum/#!topic/personal-kdbplus/JyfIxghrZb8

In general, operations on splayed tables are different from that on in-memory tables.

If you do hate to construct the table to be upserted manually, why not try something like this?

my_upsert:{[tab;data] tab upsert @[98h=t:type data;data;flip cols[tab]!data] }

3

If you want to avoid having to specify column names then you can just specify the column files and upsert to them with each both

q):hdb/progress/ set ( method:sym?ONETWOTWO; distance:1552 1623 1223)q)(sv’[;:hdb/progress/,‘methoddistance]) upsert’ (sym?TWOTHREE;1678 999)q)\l hdbq)progressmethod distance---------------ONE 1552 TWO 1623 TWO 1223 TWO 1678 THREE 999 q)distinct type ''[progress] //Check still enumeratedmethod distance----------------20 -7 // If you have a partitioned structure remember to enumerate correctlyq)tab:([]sym:100?3;col1:100?10)q).Q.dpft[:hdb2/;.z.D;sym;tab]q)(sv'[;:hdb2/2018.28.09,'symcol1]) upsert' (:hdb2/sym?3?4;3?10)

As always, consider the impact of any upserts you are making, you don’t want to lose your grouped attribute by upserting (as my second example is doing)