CODEWITHSUNDEEP
insert-updatekdb+
Jean-Paul
Jean-Paul

Reputation: 21180

kdb+ conditional insert: only insert when column value doesn't exist

What is the best way to insert a row into a table, only if a column of that row doesn't exist in the table.

E.g.:

q)table:([] col1:(); col2:(); col3:());
q)`table insert (1;2;3);
q)conditionalInsert:{if[first where table.col1=x(0)~0N;`table insert x]};

Now when doing the following:

q)conditionalInsert[(1;2;3)];
q)conditionalInsert[(7;8;9)];

The result yields:

q)table
col1 col2 col3
--------------
1    2    3
7    8    9

This can probably be accomplished more easily. My question: what is the easiest/best way?

To be clear: the column may be a non-keyed one.

Or in other words: Table is either keyed or non keyed and target column is not a key (or part of the compound key columns)

Upvotes: 1

Views: 1404

Answers (2)

Rahul
Rahul

Reputation: 3969

First thing is to have proper attributes (sort,group) on the target column which will make function faster.

Now there are 2 scenarios I can think of:

a) Table is keyed and target column is keyed column : In this case normal insert will work in way like your conditional insert.

b) Table is either keyed or non keyed and target column is not a key (or part of the compound key columns) :

         q)conditionalInsert: {if[not x[0] in table.col1;`table insert x]}

Its better to use 'exec' in place of 'table.col1' as dot notation doesn't work for keyed table:

         q)conditionalInsert: {if[not x[0] in exec col1 from table;`table insert x]}

Upvotes: 2

Akash
Akash

Reputation: 131

Use a keyed table?

q)table  
col1| col2 col3  
----| ---------  
1   | 2    3  
q)`table insert (1;2;4)  
'insert  
q)`table insert (2;2;4)  
,1  
q)table  
col1| col2 col3  
----| ---------  
1   | 2    3  
2   | 2    4

you can always use protected evaluation to silent the error. 

q).[insert;(`table;(1;2;4));{`already_there}]  
`already_there  
q).[insert;(`table;(3;2;4));{`already_there}]  
,2  
q)table  
col1| col2 col3  
----| ---------  
1   | 2    3  
2   | 2    4  
3   | 2    4

Upvotes: 3

Related Questions