kdb - functional update where multiple columns from table A match multiple columns from table B

Question

I have the following 2 sample tables. I would like to turn the q-sql statement below into a functional update...

A:([]orderID:`Aa`Bb`Cc`Bb`Bb`Cc`Aa; date:2016.10.26 2004.09.30 2004.03.10 2016.11.08 2004.02.14 2010.06.01 2008.05.01; number:9 4 5 4 2 7 8; name:`Emp1`Emp2`Emp3`Emp2`Emp2`Emp3`Emp1; cost:100.7 99.8 84.2 85.5 73.9 91.6 94.1; ID:0 1 1 0 1 1 1)

B:([]oid:`Aa`Dd`Cc`Ee`Bb`Cc`Aa; dte:2009.10.26 2020.04.04 2020.03.06 2006.09.28 2004.02.14 2019.09.27 2012.04.30; num:9 5 7 8 2 6 1; nme:`Emp1`Emp2`Emp1`Emp2`Emp2`Emp4`Emp1; cst:100.7 69.8 74.2 85.5 73.9 81.6 94.1; ID:5 5 7 5 7 5 5)

update ID:5 from A where {[a;b;c;d;e]a,b,c,d,e}'[orderID;date;number;name;cost] in (exec {[a;b;c;d;e]a,b,c,d,e}'[oid;dte;num;nme;cst] from B where ID=5)

If I run a parse on it I receive the following...

!
`A
,,(in;((';{[a;b;c;d;e]a,b,c,d,e});`orderID;`date;`number;`name;`cost);(?;`B;,,(=;`ID;5);();,((';{[a;b;c;d;e]a,b,c,d,e});`oid;`dte;`num;`nme;`cst)))
0b
(,`ID)!,5

How do I translate the k constraint statement here to work in a functional update?

I tried the following but it didn't update any results when I checked...

![`A;enlist(in;((';{[a;b;c;d;e]a,b,c,d,e});`orderID;`date;`number;`name;`cost);(?[`B;enlist(=;`ID;5);();enlist enlist((';{[a;b;c;d;e]a,b,c,d,e});`oid;`dte;`num;`nme;`cst)]));0b;(enlist`ID)!enlist 5i]

My goal is to update the ID column of table A if orderID, date, number, name, cost match the values of oid, dte, num, nme, cst from table B. If not, then leave as is.

Preferred output would look like the following...

q)A
orderID date       number name cost  ID
---------------------------------------
Aa      2016.10.26 9      Emp1 100.7 5
Bb      2004.09.30 4      Emp2 99.8  1
Cc      2004.03.10 5      Emp3 84.2  1
Bb      2016.11.08 4      Emp2 85.5  0
Bb      2004.02.14 2      Emp2 73.9  1
Cc      2010.06.01 7      Emp3 91.6  1
Aa      2008.05.01 8      Emp1 94.1  1

Thank you.

We can more easily help if we knew more about the tables you're comparing and updating. Please provide a code snippet to generate A and B. It would also be helpful to know why you want this to be functional statement - are you looking to build a function that can take column names of A and B? — Jorge Sawyer
– Jorge Sawyer, Commented Apr 4, 2020 at 13:12
@JorgeSawyer - updated with tables and comments above. Let me know if that is helpful. I'm open to other solutions aside from functional update. Though in reality my tables are millions of rows and I'll need to functionalize the solution so that the value being used to update my ID column will not always be constant, e.g. take the ID from each row of B and if the necessary column values equal each other than update the ID value of A with that of the ID value of B. In this case I'll do an exec distinct ID from B to get each value and then pass that list into the function to run on each value. — grokpronoia
– grokpronoia, Commented Apr 4, 2020 at 14:30

Jorge Sawyer · Accepted Answer · 2020-04-04 15:50:36Z

3

If I understand correctly, you would like to update the value of the ID column in A to match the value in B whenever the other columns match.

This sounds more like a job for a left join than an update statement. Given the tables you generate above:

q)show A:([]orderID:`Aa`Bb`Cc`Bb`Bb`Cc`Aa; date:2016.10.26 2004.09.30 2004.03.10 2016.11.08 2004.02.14 2010.06.01 2008.05.01; number:9 4 5 4 2 7 8; name:`Emp1`Emp2`Emp3`Emp2`Emp2`Emp3`Emp1; cost:100.7 99.8 84.2 85.5 73.9 91.6 94.1; ID:0 1 1 0 1 1 1)
orderID date       number name cost  ID
---------------------------------------
Aa      2016.10.26 9      Emp1 100.7 0
Bb      2004.09.30 4      Emp2 99.8  1
Cc      2004.03.10 5      Emp3 84.2  1
Bb      2016.11.08 4      Emp2 85.5  0
Bb      2004.02.14 2      Emp2 73.9  1
Cc      2010.06.01 7      Emp3 91.6  1
Aa      2008.05.01 8      Emp1 94.1  1
q)show B:([]oid:`Aa`Dd`Cc`Ee`Bb`Cc`Aa; dte:2009.10.26 2020.04.04 2020.03.06 2006.09.28 2004.02.14 2019.09.27 2012.04.30; num:9 5 7 8 2 6 1; nme:`Emp1`Emp2`Emp1`Emp2`Emp2`Emp4`Emp1; cst:100.7 69.8 74.2 85.5 73.9 81.6 94.1; ID:5 5 7 5 7 5 5)
oid dte        num nme  cst   ID
--------------------------------
Aa  2009.10.26 9   Emp1 100.7 5
Dd  2020.04.04 5   Emp2 69.8  5
Cc  2020.03.06 7   Emp1 74.2  7
Ee  2006.09.28 8   Emp2 85.5  5
Bb  2004.02.14 2   Emp2 73.9  7
Cc  2019.09.27 6   Emp4 81.6  5
Aa  2012.04.30 1   Emp1 94.1  5

I see that the fifth row in A matches a row in B, so the fifth row in A should be updated with the corresponding value in B: 7. You could use a left join to achieve this like so:

q)A lj 5!cols[A] xcol B
orderID date       number name cost  ID
---------------------------------------
Aa      2016.10.26 9      Emp1 100.7 0
Bb      2004.09.30 4      Emp2 99.8  1
Cc      2004.03.10 5      Emp3 84.2  1
Bb      2016.11.08 4      Emp2 85.5  0
Bb      2004.02.14 2      Emp2 73.9  7
Cc      2010.06.01 7      Emp3 91.6  1
Aa      2008.05.01 8      Emp1 94.1  1

To briefly explain the above: the xcol function forces the schema of B to match A, and the (!)enkey operator sets the first five columns as the keys. Finally, the left join performs the update you desire.

If you would like to focus on updating only a single ID value at a time you could filter on B with a functional select before left joining:

A lj 5!cols[A] xcol ?[B;enlist(=;`ID;5);0b;()]

Do let me know if I misunderstood anything. Your output table doesn't quite match what I understood to be your goal. Was that first column supposed to match in both A and B?

edited Apr 4, 2020 at 15:50

answered Apr 4, 2020 at 15:21

Jorge Sawyer

1,3416 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

grokpronoia Over a year ago

Thanks @Jorge. That's very helpful. In addition, what would be the best way to mark the rows from table A with an ID (e.g.,99) that DO NOT appear in table B, i.e., in the above example rows 1 2 3 4 6 7 (all except 5). Thank you!

Jorge Sawyer Over a year ago

Fortunately the in keyword works on rows of tables, so you can take advantage of it here. not (delete ID from A) in (delete ID from cols[A] xcol B) will give you a boolean vector of the rows you'd like to update, which you could feed into a functional update statement.

C.Ross · Accepted Answer · 2020-04-04 15:44:44Z

3

q)A:([]orderID:`Aa`Bb`Cc`Bb`Bb`Cc`Aa; date:2009.10.26 2004.09.30 2004.03.10 2016.11.08 2004.02.14 2010.06.01 2008.05.01; number:9 4 5 4 2 7 8; name:`Emp1`Emp2`Emp3`Emp2`Emp2`Emp3`Emp1; cost:100.7 99.8 84.2 85.5 73.9 91.6 94.1; ID:0 1 1 0 1 1 1)
q)A lj 5!cols[A] xcol select from B where ID=5
orderID date       number name cost  ID
---------------------------------------
Aa      2009.10.26 9      Emp1 100.7 5
Bb      2004.09.30 4      Emp2 99.8  1
Cc      2004.03.10 5      Emp3 84.2  1
Bb      2016.11.08 4      Emp2 85.5  0
Bb      2004.02.14 2      Emp2 73.9  1
Cc      2010.06.01 7      Emp3 91.6  1
Aa      2008.05.01 8      Emp1 94.1  1

answered Apr 4, 2020 at 15:44

C.Ross

2511 silver badge3 bronze badges

Collectives™ on Stack Overflow

kdb - functional update where multiple columns from table A match multiple columns from table B

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related