How to save the result of sql query against caches into another cache

Question

I have two caches, and I did a join between these two caches. Then what is the recommended/efficient way to save the result of this join query into the 3rd cache?

I would like not to do it by iterating over the query result and put each into the cache,but would like to use sql like

insert into thirdcache select colomns from firstcache join secondcache

Answer 1

You can use "insert from select" sql query, but you should specify fields to be updated in insert part and also types of these fields should be equals to corresponding columns types returned by select part.

Smth like this:

insert into t1 (colA, colB) (Select t2.colX, t2.colY from t2);

where colA type equal to colX and etc.