Upsert from query

[Birzos]

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.

[Jamie_O__Mahony]

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

[Birzos]

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

[Flying]

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

[Birzos]

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.

[Flying]

`tab1 upsert 0!tab2

[Birzos]

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