0

Let's see the definition of upsert:

q)upsert
.[;();,;]

So it is just an Amend Entire:

.[d;();v;y]   <=>   v[d;y]

q).[1 2; (); ,; 3 4 5]
1 2 3 4 5

It looks like join , under the hood. But applying the same approach to adding some rows to a table gives different results:

q)show t:((`a`b!1 2);(`a`b!3 4))
a b
---
1 2
3 4
q).[t;();,;(5 6;7 8)]
a b
---
1 2
3 4
5 6
7 8
q),[t;(5 6;7 8)]
`a`b!1 2
`a`b!3 4
5 6
7 8

For some reason q doesn’t want to join , list of lists to the table in the same way as in Amend Entire. I’m wondering why.

Could you give me some directions please?

egor7
  • 4,678
  • 7
  • 31
  • 55

2 Answers2

2

When you use a plain comma join with a table and a list, you are joining two different structures and get the result you would probably expect, as your last example shows.

When you use amend, you are telling q to modify the table, so it is able to join the lists into the table.

I think about it as,

  • join without amend - simply joining two objects, it depends on whether they conform or not, of how the final result will look
  • join with amend - you are specifically telling q to modify the first object by joining the second, it will do what it can to allow this to work if the type of the first object can be retained after the join, otherwise it will error.
Adam Bonham
  • 615
  • 4
  • 7
2

One should draw an analogy between amend entire and assign through operator, not join:

q)show t:((`a`b!1 2);(`a`b!3 4))
a b
---
1 2
3 4
q),:[t;(5 6;7 8)]
q)t
a b
---
1 2
3 4
5 6
7 8

As you can see, the latter is not the same as

q)t:((`a`b!1 2);(`a`b!3 4))
q)show t:t,(5 6;7 8)
`a`b!1 2
`a`b!3 4
5 6
7 8

The section I linked even mentions that ,: "is syntactic sugar for Amend At."

Igor Korkhov
  • 8,283
  • 1
  • 26
  • 31