cancel
Showing results for 
Search instead for 
Did you mean: 

select rows from table based on tuple match on disk

Sam1
New Contributor
Consider two tables:

t1:([]ts:4 5 6; a:1 2 3;b:10 20 30;c:`a`b`c)
t2:([]ts:0 1 2 3; a:2 3 4 5;b:20 30 40 50;c:`b`c`d`e)

I want to select those rows in t1 such that the tuple (a;b) does not exist already in t2 (a;b). So in the above case, it should retrieve 
a b  c
------
1 10 a

t2 is a very large memory mapped table. t1 are the new entries to be inserted into t2. I only want to insert those entries when (a;b) are globally unique. 

Any ideas how to achieve this?   Keying is not possible as all tables are splayed on disk.

thansk
3 REPLIES 3

Tom_Martin1
New Contributor III
Something like this?

q)t1:([]ts:4 5 6; a:1 2 3;b:10 20 30;c:`a`b`c)
q)t2:([]ts:0 1 2 3; a:2 3 4 5;b:20 30 40 50;c:`b`c`d`e)

q)select from t1 where not flip[(a;b)] in flip exec (a;b) from t2
ts a b  c
---------
4  1 10 a

select from t1 where not([]a;b)in`a`b#t2

TerryLynch
New Contributor II
That's not actually matching the (a;b) pairs, it just by chance works in this example because the c columns match. 

If the t2 table was t2:([]ts:0 1 2 3; a:2 3 4 5;b:20 30 40 50;c:`x`y`d`e) then

q)(delete ts from t1) except (delete ts from t2)
a b  c
------
1 10 a
2 20 b
3 30 c

doesn't exclude the (2;20) and (3;30) pairs