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 [email protected]. For more options, visit https://groups.google.com/d/optout.
