how to link results to a correct rows in a partitioned table kdb

Question

I have a column of dates based on the below conditions in lst and runs fine and I need to link it by set or upsert or whatever means to the appropirate rows to the partitioned database.

(These are partitioned tables for the corresponding days made of many columns)

tbp:2016.11.01 
tb:2016.11.02

lst: distinct select  b_market_order_no,instrumentID,mkt from tb  where event=`OvernightOrder

I get a column of dates based on the conditions in lst, now I have to link it to the appropriate rows for table, tb, in the database. What I have down for "set" command will simply add the dates blindly, I guess to each row until it is used up. Not sure how to pass the appropriate row location to set or even if that is the right way to do it. Or upsert needs to be used! Or there is totally a different needs to be implemented!

(`$":PATH/2016.11.02/order/b_orig_date") set select b_orig_date from tbp where ([]b_market_order_no;instrumentID;mkt) in lst

Answer 1

I think this is what you want, also for simplicity I'm referring to the table as order with appropriate date where clauses:

Edit: added .Q.par also note the 3! as need to have b_market_order_no, instrumentID, mkt all as the keyed columns

Edit: Generalised use if you have the hdb loaded into a q process:

dirPath:string .Q.par[`:.;2016.11.02;`order]
(`$dirPath,"/b_orig_date") set exec b_orig_date from 
  (select b_market_order_no, instrumentID, mkt from order where date = 2016.11.02) lj 
  3!select b_market_order_no, instrumentID, mkt, b_orig_date from order where 
    date = 2016.11.01,([]b_market_order_no;instrumentID;mkt) in lst

Also if this is an entirely new column for 2016.11.02/order, You will need to edit .d

(`$dirPath,"/.d") set (get `$dirPath,"/.d"),`b_orig_date

This column may also be needed to be added throughout a hdb using dbmaint.q

https://github.com/KxSystems/kdb/blob/master/utils/dbmaint.q

Answer:

dirPath:string .Q.par[`:.;2016.11.02;`order]
(`$dirPath,"/b_orig_date") set exec b_orig_date from 
  (select b_market_order_no, instrumentID, mkt from tb) lj 
  3!select b_market_order_no, instrumentID, mkt, b_orig_date from tbp where 
  ([]b_market_order_no;instrumentID;mkt) in lst