Hello Sidds, UPDATE with JOINs is not supported in Ignite as long as H2 database grammar Ignite relies on does not support it too [1]. AFAIK, they are not part of ANSI SQL 99 either, and thus different DB engines have different syntax and capabilities to support it. However, I believe I understand what you're trying to do and would like to give you a good workaround.
Could you please explain what are you trying to achieve? It looks like by this join you're trying to additionally limit some range of status codes that you want to be affected, is that right? If it is, I'd recommend you to use IN instead of JOIN, here's what your query would look like in this case: update fulfill_order fo set fo.fulfill_order_status_code =?, fo.last_update_userid =?, fo.order_due_ts =?, fo.last_update_ts =? where fo.fulfill_order_id=? AND fo.fulfill_order_status_code IN ( SELECT TABLE.STATUS FROM TABLE(STATUS=?) ) Note that last line written in caps after AND - it introduces a fake subselect query. Thus, you'll need to supply your array of filtering args as the LAST parameter of this query. (You call corresponding variable "list" in your code.) Regards, Alex [1] http://www.h2database.com/html/grammar.html#update 2017-09-12 13:41 GMT+03:00 iostream <sidds.mo...@gmail.com>: > Hi, > > Does ignite v2.1 support UPDATE queries with JOINS? > > I tried a SELECT query as follows and it worked fine - > > select count(*) from fulfill_order fo join table(id bigint = ?) t on > fo.fulfill_order_status_code = t.id"; > SqlFieldsQuery enhanceQuery = new SqlFieldsQuery(cacheQuery); > ArrayList<Integer> list = new ArrayList<>(); > list.add(7); > list.add(1); > list.add(9); > Integer [] arr = list.toArray(new Integer[list.size()]); > Object [] obj = new Object[]{arr}; > enhanceQuery.setArgs(obj); > IgniteCache<fulfill_order_key, fulfill_order> fulfillOrderCache = > Ignition.ignite() > > .cache(CacheNameConstants.FULFILL_ORDER_CACHE_NAME); > QueryCursor<List<?>> cursor = fulfillOrderCache.query(enhanceQuery); > > However, I tried running UPDATE query as follows but none of the queries > worked. > > 1. String updateQuery = "UPDATE fo SET fo.fulfill_order_status_code =? " > + "FROM fulfill_order fo join table(id bigint = ?) t > on fo.fulfill_order_status_code = t.id " > + "where fo.fulfill_order_id=?"; > SqlFieldsQuery enhanceQuery = new SqlFieldsQuery(updateQuery); > ArrayList<Integer> list = new ArrayList<>(); > list.add(7); > list.add(1); > list.add(9); > Integer [] arr = list.toArray(new Integer[list.size()]); > Object [] obj = new Object[]{arr}; > enhanceQuery.setArgs(3,obj, 347427284695L); > IgniteCache<fulfill_order_key, fulfill_order> fulfillOrderCache = > Ignition.ignite() > > .cache(CacheNameConstants.FULFILL_ORDER_CACHE_NAME); > fulfillOrderCache.query(enhanceQuery); > > 2. update fulfill_order fo join table(id bigint = ?) t on > fo.fulfill_order_status_code = t.id > set fo.fulfill_order_status_code =?, fo.last_update_userid =?, > fo.order_due_ts =?, > fo.last_update_ts =? where fo.fulfill_order_id=? > > Can someone help with the correct way of running UPDATE query with JOIN? > > Thanks! > > > > -- > Sent from: http://apache-ignite-users.70518.x6.nabble.com/
