select rows from table based on tuple match on disk

Community Support
1

Consider two tables:

t1:(ts:4 5 6; a:1 2 3;b:10 20 30;c:ab`c)

t2:(ts:0 1 2 3; a:2 3 4 5;b:20 30 40 50;c:bcde)

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

2

Something like this?

q)t1:(ts:4 5 6; a:1 2 3;b:10 20 30;c:ab`c)

q)t2:(ts:0 1 2 3; a:2 3 4 5;b:20 30 40 50;c:bcde)

q)select from t1 where not flip[(a;b)] in flip exec (a;b) from t2

ts a b  c

4  1 10 a

3

select from t1 where not(a;b)inab#t2

4

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:xyde) 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