SQL mindset

Community Support
1

I am using SQL regularly among other languages. So I am approaching kdb+ with that mindset. There are a couple of constructs in SQL that I like and use as I find them helpful.

  1. MERGE: 

MERGE target_table T

USING source_table S

ON T.key1=S.key1 AND T.key2=S.key2

WHEN MATCHED THEN UPDATE SET T.value1=S.value1

WHEN NOT MATCHED BY TARGET THEN INSERT (value1) VALUES (S.value1)

WHEN NOT MATCHED BY SOURCE THEN DELETE;

See http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm#SQLRF01606

I searched this forum but couldn’t find anything related. To get the same result in kdb+, do you have to use separate insert/update/delete statements like in older SQL days?

  1. In SQL you can have defaults for columns and as such you don’t have to specify values for all columns when inserting a row into a table. Is there anything like that in kdb+?

  2. I am guessing kdb+ doesn’t have the concept of auto-incremented column, right? Probably not much needed anyway or easily simulated, but just wanted to make sure.

2

In SQL you also have ‘EXISTS (SELECT 1 FROM table)’. Is this to be emulated by ‘count table’? Is count in kdb+ a quick operation? In SQL it looks like everytime you run count it actually counts all the elements in the table, so if the table is big, count may take a while to return.

3

For #1

T:([key1:ab;key2:de]c:0 1)

S:([key1:ac;key2:df]c:2 3)

(key[S]#T)^S

#2

t:(a:ab;b:0 1;c:“ab”)

t,:(a:cd;c:“cd”)

#3

no auto-increment

4

count is fast - i think it’s ok to assert that count will return the same value for everyone looking at the same table at the same time.  ie. no version control built in