Kdb upsert with conditional syntax?

Question

Is there a way I can upsert in kdb where the following occurs:

1. If key is not present, insert values
2. If key is present, check if current value is greater
   A) If so, perform no action
   B) If not, update values

Something like:

job upsert ([title: job1] time: enlist 1 where time > 1)

Answer 1

For a table, a key, and a value: upsert the tuple if the key is new or the value exceeds the existing value.

q)t:([job:`a`b`c] val: 4 4 4)    /current table
q)t[`a]|:6                       /old key, higher value
q)t
job| val
---| ---
a  | 6
b  | 4
c  | 4
q)t[`c]|:1                       /old key, lower value
q)t
job| val
---| ---
a  | 6
b  | 4
c  | 4
q)t[`d]|:5                       /new key
q)t
job| val
---| ---
a  | 6
b  | 4
c  | 4
d  | 5

Remarks

A keyed table with a single data column could perhaps be a dictionary.

Amending through an operator works also with a new key.

Upserting a table (or dictionary) of new records is more efficient and simpler than updating a single tuple.

q)nt:([job:`a`c`d]val: 6 1 5)    /new values to check
q)t|nt                           /maximum of two tables
job| val
---| ---
a  | 6
b  | 4
c  | 4
d  | 5

or just

q)t[([]job:`a`c`d)]|:([]val:6 1 5)

Simple-looking primitives such as maximum (|) repay careful study.

Answer 2

Since you're using a keyed table, and you want to change values only if they're greater and add in new keys and values, you can try avoiding upsert entirely:

t:([job:`a`b`c] val: 4 4 4)    /current table
nt:([job:`a`c`d]val: 6 1 5)    /new values to check

t|nt
job| val
---| ---
a  | 6
b  | 4
c  | 4
d  | 5

This will automatically add keys that aren't there, and update the current value to the new value if the new value is larger.

Answer 3

please find a solution and explanation below. I'll edit if I come up with a better way - thanks. *also I hope I interpreted the question correctly.

q)t1
name   | age height
-------| ----------
michael| 26  173
john   | 57  156
sam    | 23  134
jimmy  | 83  183
conor  | 32  145
jim    | 64  167
q)t2
name age height
---------------
john 98  220
mary 24  230
jim  50  240
q)t1 upsert t2 where{$[all null n:x[y`name];1b;y[`age]>n[`age]]}[t1;]each t2
name   | age height
-------| ----------
michael| 26  173
john   | 98  220
sam    | 23  134
jimmy  | 83  183
conor  | 32  145
jim    | 64  167
mary   | 24  230
q)

Explanation; The function takes 2 args, x = the keyed table t1 and y = each record from t2(as a dictionary). First we extract the name value from the t2 record(y`name) and try to index into the source keyed table with that value and store the result in the local variable n. If the name exists, the corresponding record(n, as a dictionary)will be returned from y(and all null n will be false) otherwise an empty record will be returned(and all null n will be true). If we cannot find an instance of the t2[`name] in t1 then we just return 1b from the function. Otherwise, then we want to compare the ages between the two records (n[`age] <-- age referenced in t1 for the matching name & y[`age] <-- age of this particular record of t2) - if the age for this matching record in t2 (y[`age]) is greater than the matching value from t1 then we return 1b otherwise we return 0b.

The result of this function is a list of booleans, one for each record in t2. 1b is returned under 2 scenarios - either; (1) This particular name from t2 has no match in t1. (2) This name from t2 does have a match in t1 and the age is greater than the corresponding age in t1. 0b is returned when the age referenced in t2 is less than the corresponding age from t1.

In our example the result of the function is 110b and after we apply where to this, the result is the indexes where the list value is true i.e. where 110b --> 0 1. We use this list to index into t2 which returns the first 2 records from t2(these are either new records or records where the age is greater than what is referenced in t1), then we simply upsert this into t1.

I hope this helps and hope some better solutions come along.