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.
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?