Re: UPDATE query with JOIN

2019-06-25 Thread Maxim.Pudov
Hey, something is wrong in your sample SELECT DISTINCT t1.ID AS ID FROM PUBLIC.AAA_STR_HIE_ACCOUNT t1 LEFT JOIN PUBLIC.AAA_STR_HIE_ACCOUNT t2 ON t2.PARENT = t1.ID AND t2.hier = t1.hier WHERE t2.PARENT IS NULL AND t1.hier = 'H4'; The resultset will be either empty or one row with null in it, but

Re: UPDATE query with JOIN

2019-06-10 Thread Marcel Frey
Just to add one more finding. If I execute my query in one piece, runtime is 10 to 15 seconds (for updating 1252 rows) UPDATE PUBLIC.AAA_STR_HIE_ACCOUNT SET MBR_TYPE = 'B' WHERE ID IN ( SELECT DISTINCT t1.ID FROM PUBLIC.AAA_STR_HIE_ACCOUNT t1 LEFT JOIN PUBLIC.AAA_STR_HIE_ACCOUNT t2 ON t2.PAREN

Re: UPDATE query with JOIN

2019-06-10 Thread mfrey
Hi, is there a plan to implement this (join on UPDATE) or to improve the performance of the "IN" operator ? I have an UPDATE query with two "IN" operators. UPDATE PUBLIC.AAA_STR_HIE_ACCOUNT SET MBR_TYPE = 'B' WHERE ID IN ( SELECT ID ...'

Re: UPDATE query with JOIN

2019-01-14 Thread ilya.kasnacheev
Hello! Unfortunately you can't do this right now. Your best bet is 4) as you have figured. Regards, -- Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: UPDATE query with JOIN

2018-10-09 Thread Justin Ji
Anyone who can tell me how to do an update operation with *join table*? I have tried the SQL below: 1.update A set A.a1=1 join table(devId varchar=?) B on A.devId=B.devId *SQL ERROR* 2.update A, table(devId varchar=?) B set A.a2=1 where A.devId=B.devId *SQL ERROR* 3.update A set A.a1=1 where A.dev

Re: UPDATE query with JOIN

2018-10-08 Thread Justin Ji
Thank for your reply! I sure I have indexed the devId, here is the output of EXPLAIN: [SELECT __Z0.DEVID AS __C0_0, __Z0.ISONLINE AS __C0_1, __Z0.MQTTTIME AS __C0_2 FROM "device_online_status".T_DEVICE_ONLINE_STATUS __Z0 /* "device_online_status".T_DEVICE_ONLINE_STATUS_DEVID_ASC_ID

Re: UPDATE query with JOIN

2018-10-08 Thread Evgenii Zhuravlev
Hi, Are you sure that you have index for devId field? Evgenii пн, 8 окт. 2018 г. в 12:23, Justin Ji : > Hi Alexander - > > I have tried the SQL you suggested, but the performance got worse, I do not > know why? > > 1. "update t_device_module set isOnline=1, mqttTime=" + > System.currentTimeMill

Re: UPDATE query with JOIN

2018-10-08 Thread Justin Ji
Hi Alexander - I have tried the SQL you suggested, but the performance got worse, I do not know why? 1. "update t_device_module set isOnline=1, mqttTime=" + System.currentTimeMillis() / 1000 + " where devId in ('0001', '0002', ..., '2048')"; The SQL may take 600ms for 2048 records. 2."update t

Re: UPDATE query with JOIN

2018-10-08 Thread Justin Ji
Hi Alexander - I have tried the SQL you suggested, but the performance got worse, I do not know why? 1. "update t_device_module set isOnline=1, mqttTime=" + System.currentTimeMillis() / 1000 + " where devId in ('0001', '0002', ..., '2048')"; The SQL may take 600ms for 2048 records. 2."update t

Re: UPDATE query with JOIN

2017-09-14 Thread iostream
Hi Alexander, I will try what you have suggested. Thanks for the suggestions! -- Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: UPDATE query with JOIN

2017-09-14 Thread Alexander Paschenko
t = ?) 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/

UPDATE query with JOIN

2017-09-12 Thread iostream
Can someone help with the correct way of running UPDATE query with JOIN? Thanks! -- Sent from: http://apache-ignite-users.70518.x6.nabble.com/