Upsert from query

Community Support
1

How would you upsert from a query where the query is the same output as the target table, have been through all the documentation and could not find one example.

2

Hi Birzos,

Could you provide an example of the query?

Does the following help you out:

q)tab:(a:1 2 3)

q)`tab upsert select from tab

`tab

q)tab

a

1

2

3

1

2

3

Thanks,

Jamie

3

Thanks. This is an example someone else provided, the goal is to store the aggregate in to an ohlc table for post processing but with a single command, similar to “insert into tab1 select sym,date,open,high,low,close from tab2”.

update preclose:prev close from select max(datetime),min(datetime),Open:first price,High:max price,Low:min price,Close:last price,Volume:count(i) by DT:datetime.date+1 xbar datetime.minute from base where instrument=GBPUSD

4

`tab1 upsert select sym,date,open,high,low,close from tab2

5

That works fine, the problem is that the upsert doesn’t like the ‘by’ clause which creates the instrument and datetime, it states wrong type.

6

``tab1 upsert 0!tab2`

7

Found it, the datetime was not casting due to the xbar, hence the wrong type, works perfectly with the code below.

`ohlc upsert

    select Open:first price,High:max price,Low:min price,Close:last price,Volume:count(i)

by instrument,datetime:“z”$datetime.date+1 xbar datetime.minute

        from base where instrument=GBPUSD