You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by DS <de...@aline-consulting.com> on 2018/12/27 09:49:46 UTC

Is Apache planning to release support for table joins with updates .

I am  looking solution  for queries  like below  :

Update Person SET  lastName ='pit' where person.id =city.id AND city.zipCode
='B8Q97'

OR

UPDATE table1, table2 SET table1.quantity = 6 where table1.pid =table2.pid
AND  table2.zip ="abc"





--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: Is Apache planning to release support for table joins with updates .

Posted by Denis Mekhanikov <dm...@gmail.com>.
Try substituting personalDetails.F_id with a value from the record,
that you expect to be updated, and try running the subquery on its own.
Make sure, that the result is not empty.

Denis

пт, 28 дек. 2018 г. в 13:14, DS <de...@aline-consulting.com>:

> Denis Mekhanikov wrote
> > Is it a single query? Why not execute these two updates separately?
> > I don't see any problems in the first one.
> >
> > The second one needs some refactoring though.
> > Ignite uses an SQL query engine of H2, which doesn't support JOINs in
> > UPDATE statements.
> > But you may change it in the following way:
> >
> > UPDATE table1 SET table1.quantity = 6 WHERE EXISTS
> >
> >     (SELECT * FROM table2 WHERE table2.pid=table1.pid AND table2.zip
> > ='abc')
> > ;
> >
> >
> > Denis
> >
> > чт, 27 дек. 2018 г. в 12:49, DS &lt;
>
> > deepika.singh@
>
> > &gt;:
> >
> >> I am  looking solution  for queries  like below  :
> >>
> >> Update Person SET  lastName ='pit' where person.id =city.id AND
> >> city.zipCode
> >> ='B8Q97'
> >>
> >> OR
> >>
> >> UPDATE table1, table2 SET table1.quantity = 6 where table1.pid
> >> =table2.pid
> >> AND  table2.zip ="abc"
> >>
> >>
> >>
> >>
> >>
> >> --
> >> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
> >>
>
>  Query i ran  over GridGain :
>
> UPDATE personalDetails SET personalDetails.age = 26
> WHERE EXISTS (SELECT * FROM cityDEtails WHERE
>  cityDEtails.id=personalDetails.F_id AND cityDEtails.pincode = 560102)
>
> It ran but didn't update the value ?? .
>
> any idea why?
>
>
>
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>

Re: Is Apache planning to release support for table joins with updates .

Posted by DS <de...@aline-consulting.com>.
Denis Mekhanikov wrote
> Is it a single query? Why not execute these two updates separately?
> I don't see any problems in the first one.
> 
> The second one needs some refactoring though.
> Ignite uses an SQL query engine of H2, which doesn't support JOINs in
> UPDATE statements.
> But you may change it in the following way:
> 
> UPDATE table1 SET table1.quantity = 6 WHERE EXISTS
> 
>     (SELECT * FROM table2 WHERE table2.pid=table1.pid AND table2.zip
> ='abc')
> ;
> 
> 
> Denis
> 
> чт, 27 дек. 2018 г. в 12:49, DS &lt;

> deepika.singh@

> &gt;:
> 
>> I am  looking solution  for queries  like below  :
>>
>> Update Person SET  lastName ='pit' where person.id =city.id AND
>> city.zipCode
>> ='B8Q97'
>>
>> OR
>>
>> UPDATE table1, table2 SET table1.quantity = 6 where table1.pid
>> =table2.pid
>> AND  table2.zip ="abc"
>>
>>
>>
>>
>>
>> --
>> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>>

 Query i ran  over GridGain :

UPDATE personalDetails SET personalDetails.age = 26
WHERE EXISTS (SELECT * FROM cityDEtails WHERE
 cityDEtails.id=personalDetails.F_id AND cityDEtails.pincode = 560102)

It ran but didn't update the value ?? .

any idea why?

    
    



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: Is Apache planning to release support for table joins with updates .

Posted by Denis Mekhanikov <dm...@gmail.com>.
Is it a single query? Why not execute these two updates separately?
I don't see any problems in the first one.

The second one needs some refactoring though.
Ignite uses an SQL query engine of H2, which doesn't support JOINs in
UPDATE statements.
But you may change it in the following way:

UPDATE table1 SET table1.quantity = 6 WHERE EXISTS

    (SELECT * FROM table2 WHERE table2.pid=table1.pid AND table2.zip ='abc')
;


Denis

чт, 27 дек. 2018 г. в 12:49, DS <de...@aline-consulting.com>:

> I am  looking solution  for queries  like below  :
>
> Update Person SET  lastName ='pit' where person.id =city.id AND
> city.zipCode
> ='B8Q97'
>
> OR
>
> UPDATE table1, table2 SET table1.quantity = 6 where table1.pid =table2.pid
> AND  table2.zip ="abc"
>
>
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>