You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@ignite.apache.org by "aaron@tophold.com" <aa...@tophold.com> on 2017/07/19 13:02:00 UTC

Did Ignite query support embed DML ?

hi All, 

Will Ignite support Embed SUM DML Like this SQL updates:

UPDATE A a
SET a.total=(
    SELECT SUM(b.cnt)
    FROM B b
    WHERE b.id = a.id
)


Always throw a fail to parse SQL query exception . 




aaron@tophold.com

Re: Re: Did Ignite query support embed DML ?

Posted by vkulichenko <va...@gmail.com>.

Aaron,

Please show the full exception trace and attach your configuration.

-Val



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Did-Ignite-query-support-embed-DML-tp15120p15973.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: Re: Did Ignite query support embed DML ?

Posted by "aaron@tophold.com" <aa...@tophold.com>.

Thanks Val, 

I need the SUM  result set back to the table A. 

I try MERGE INTO;   but seem Ignite not work like standard SQL. 

Even I use MERGE I have to include all other column if any present in table A.    like below table.

MERGE INTO A (_key, total) ( 
  SELECT b.id,  SUM(b.count), FROM 
  cacheB.B b
  GROUP BY ap.accountId 
)

It always throw exception the A'a anotherColumn should not be NULL.  

Thanks for your time!

Regards
Aaron

-----------------------
A:

id,  total, anotherColumn
abc, , 0,  12345
xyz, 1, 6789

B:

id, count
abc, 1
abc, 2
abc, 5
xyz, 3
xyz, 1


Regards
Aaron


aaron@tophold.com
 
From: vkulichenko
Date: 2017-07-21 02:44
To: user
Subject: Re: Re: Did Ignite query support embed DML ?
If you just calculate the sum, then you don't need to join, right? Just
remove the WHERE clause.
 
-Val
 
 
 
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Did-Ignite-query-support-embed-DML-tp15120p15198.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: Re: Did Ignite query support embed DML ?

Posted by vkulichenko <va...@gmail.com>.

If you just calculate the sum, then you don't need to join, right? Just
remove the WHERE clause.

-Val



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Did-Ignite-query-support-embed-DML-tp15120p15198.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: Re: Did Ignite query support embed DML ?

Posted by "aaron@tophold.com" <aa...@tophold.com>.

Thanks Val, 

This A is the parent table, B is children table,  we want to SUM the value in B table and set back to the A. 

they join by the id,   possible data like this

A:

id,  total
abc, 0
xyz, 1

B:

id, count
abc, 1
abc, 2
abc, 5
xyz, 3
xyz, 1

Thanks for your time!

Regards
Aaron


aaron@tophold.com
 
From: vkulichenko
Date: 2017-07-20 06:10
To: user
Subject: Re: Did Ignite query support embed DML ?
You don't have table A in the subquery, I guess that's the reason for the
failure. What exactly are you trying to achieve with this query? What is the
purpose of 'WHERE b.id = a.id' clause?
 
-Val
 
 
 
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Did-Ignite-query-support-embed-DML-tp15120p15146.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: Did Ignite query support embed DML ?

Posted by vkulichenko <va...@gmail.com>.

You don't have table A in the subquery, I guess that's the reason for the
failure. What exactly are you trying to achieve with this query? What is the
purpose of 'WHERE b.id = a.id' clause?

-Val



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Did-Ignite-query-support-embed-DML-tp15120p15146.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.