We're using the latest version 2.2.14 and not planning to go back, so this option is not an option for us. So, the update statement will not work with sub-query without let support?
On Friday, December 30, 2016 at 2:36:50 AM UTC-8, Claudio Massi wrote: > > Hi, > as you can see from documentation the update/set/let syntax was > supported in 1.7-rc1 , 1.7.9 : > > http://orientdb.com/download-previous/ > OrientDB 1.7.9 September 22nd 2014 > OrientDB 1.7-rc1 February 6th 2014 > Changelog: > SQL: support for LET also in UPDATE and DELETE commands > > If I create the a class with an integer property I can see that it works > > e.g: I use integer 1 from result set of ORole > > > select mode from ORole where name = 'admin' > > ----+-----+---- > # |@RID |mode > ----+-----+---- > 0 |#-2:1|1 > ----+-----+---- > > I insert a different value in property id > > > insert into Sample set id = 3 > > select from Sample > ----+-----+---- > # |@RID |id > ----+-----+---- > 0 |#12:0|3 > ----+-----+---- > > Now I can run both update or select using let: > > > update #12:0 set id = $i[0].mode let $i = ( select mode from ORole where > name = 'admin' ) > > select from #12:0 let $i = ( select from ORole where name = 'admin' ) > where id = $i[0].mode > > ----+-----+---- > # |@RID |id > ----+-----+---- > 0 |#12:0|1 > ----+-----+---- > > The update/set/let syntax is not supported in 2.2.x according to > documentation and tests > > Il giorno venerdì 30 dicembre 2016 01:31:20 UTC+1, Borov ha scritto: >> >> Hi Claudio, >> >> I'm a bit confused with your proposal. Are you suggesting to first run a >> subquery, store the returned id and then run an update as a separate query? >> I understand I can do it this way, but is there a way to use a sub-query in >> the update statement without doing 2 queries? Or, do we have to wait for v3 >> to use update/set/let if it'll be part of the release? >> >> >> >> On Thursday, December 29, 2016 at 8:37:32 AM UTC-8, Claudio Massi wrote: >>> >>> Hi , >>> from the subquery you get a result set not an integer, so you should >>> extract the value. >>> For example >>> >>> > create class LeadMeta >>> > create property LeadMeta.creator string >>> > create property LeadMeta.leadCampaign string >>> > create property LeadMeta.category string >>> > create property LeadMeta.owner string >>> > create property LeadMeta.ownerId integer >>> >>> > create class Sample >>> > create property Sample.meta EMBEDDEDLIST LeadMeta >>> > create property Sample.id integer >>> >>> > insert into Sample set id = 1 , meta = >>> [{"creator":"a","leadCampaign":"b","category":"c", "owner":"d","ownerId":1}] >>> >>> For example in _studio table there is a metadata integer @version with >>> value 1 , so I can run a query similar to yoru subquery, with result 1 >>> >>> > select @version from _studio where @rid = #25:0 >>> >>> Now I can run >>> >>> > select from #45:0 let $i = ( select @version from _studio where @rid = >>> #25:0 ) where id = $i[0].version >>> > select from #45:0 let $i = ( select @version from _studio where @rid = >>> #25:0 ) where meta.ownerId = $i[0].version >>> >>> Using 2.2.14 I cannot run "update .. set ...let..". >>> You can use a script to run the subquery first, and store the integer, >>> the run update. >>> >>> >>> Il giorno giovedì 29 dicembre 2016 03:01:17 UTC+1, Borov ha scritto: >>>> >>>> >>>> Hi, I'm trying to run a simple update with a sub-select query, but it >>>> is just not taking the value from the sub query. I found few examples on >>>> google, but for some reason mine still not working no matter what I try. >>>> Here is my query: >>>> >>>> orientdb {db=Customers}> update #69:0 set meta.ownerId = (select id >>>> from Account where @rid = #25:1) Updated record(s) '1' >>>> in >>>> 0.003000 sec(s). >>>> >>>> orientdb {db=Customers}> select meta from #69:0 >>>> >>>> >>>> +----+-----------------------------------------------------------------------------------+ >>>> |# |meta >>>> | >>>> >>>> +----+-----------------------------------------------------------------------------------+ >>>> |0 |LeadMeta{creator: >>>> #25:0,leadCampaign:#149:0,category:#45:1,owner:#25:1,ownerId:null}| >>>> >>>> +----+-----------------------------------------------------------------------------------+ >>>> >>>> As you can see it sets "ownerId" to null. I tried get id as id[0] but >>>> didn't help. The meta.ownerId and the id are both Integers. The sub-query >>>> on its own is returning the id just fine: >>>> >>>> orientdb {db=Customers}> select id from Account where @rid = #25:1 >>>> >>>> +----+----+ >>>> |# |id | >>>> +----+----+ >>>> |0 |21 | >>>> +----+----+ >>>> >>>> Even when I tried with the hard coded id in the sub-query, it still >>>> results in null: >>>> >>>> orientdb {db=Customers}> update #69:0 set meta.ownerId = (select 21 >>>> from Account where @rid = #25:1) >>>> Updated record(s) '1' in 0.002000 sec(s). >>>> >>>> orientdb {db=Customers}> select meta from #69:0 >>>> >>>> >>>> +----+-----------------------------------------------------------------------------------+ >>>> |# |meta >>>> | >>>> >>>> +----+-----------------------------------------------------------------------------------+ >>>> |0 |LeadMeta{creator: >>>> #25:0,leadCampaign:#149:0,category:#45:1,owner:#25:1,ownerId:null}| >>>> >>>> +----+-----------------------------------------------------------------------------------+ >>>> >>>> I'm sure I'm doing something wrong, since this is a very trivial >>>> operation. Just can't see what. >>>> >>>> >>>> -- --- You received this message because you are subscribed to the Google Groups "OrientDB" group. To unsubscribe from this group and stop receiving emails from it, send an email to orient-database+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
