Was playing around with different approaches. Still have not figured out sub-selects in INSERT and UPDATE. In SELECT, it works with IN but not with "=". The sub-select seems to be returning an array. Either it is a bug or the examples on the OrientDB website may not be explaining how to use this correctly. Not sure how to pull out the RID or any other field from a sub-select.
In the example below, Users and Locations are documents, location is a LINK field in Users, there is a Locations document with an id = 1 and rid=#11:0, there is a Users document with the location field linked to #11:0. This works: SELECT FROM Users WHERE location IN (SELECT FROM Locations WHERE id="1") These do not work: SELECT FROM Users WHERE location = (SELECT FROM Locations WHERE id="1") SELECT FROM Users WHERE location = (SELECT @rid FROM Locations WHERE id="1") SELECT FROM Users WHERE location = (SELECT @rid FROM Locations WHERE id="1")[0] SELECT FROM Users WHERE location = (SELECT @rid AS rid FROM Locations WHERE id="1")[0].rid SELECT FROM Users WHERE location = (SELECT @rid AS rid FROM Locations WHERE id="1").rid On Tuesday, January 14, 2014 12:37:12 AM UTC-5, Giraldo Rosales wrote: > > Seems to also not work with INSERTs either. Not sure how to pull out the > RID from the subquery, or any field for that matter. The nested query seems > to return "[0]". What I would like to do is insert a Link to another > document that already exists. Not sure if this is a bug or if there is > another way. Using version 1.6.3 (downloaded from Git 12/27/13). > > Tried something similar to your example in the "Sub-selects" section: > https://github.com/orientechnologies/orientdb/wiki/SQL-Insert > > insert into Diver SET name = 'Luca', buddy = (select from Diver where name = > 'Marko') > > Thanks! > Giraldo > > > > On Monday, January 13, 2014 2:19:58 PM UTC-5, Giraldo Rosales wrote: >> >> Thanks for the quick response. It is throwing the following error: >> >> com.orientechnologies.orient.core.exception.OValidationException: The >> field 'Zipcodes.state' has been declared as LINK but the value is not a >> record or a record-id >> >> I switch the field from "Zipcodes.state" to "Zipcodes.state_tmp" to see >> what was the output. It seems the output is "[0]". >> >> I also tried the following but none of them seemed to work: >> UPDATE Zipcodes SET state=(SELECT @rid FROM States WHERE id= >> *$parent.state_id*) >> UPDATE Zipcodes SET state=(SELECT @rid FROM States WHERE >> id=$parent.$record.state_id)*[0]* >> UPDATE Zipcodes SET state=(SELECT @rid *AS rid* FROM States WHERE >> id=$parent.$record.state_id)*.rid* >> UPDATE Zipcodes SET state=(SELECT @rid *AS rid* FROM States WHERE >> id=$parent.$record.state_id)*.result[0].rid* >> UPDATE Zipcodes SET state=(SELECT @rid *AS state* FROM States WHERE >> id=$parent.$record.state_id) >> >> On Monday, January 13, 2014 12:09:20 PM UTC-5, Lvc@ wrote: >>> >>> Hi, >>> to get the upper level context variable you've to use $parent.record. >>> Example: >>> >>> UPDATE Zipcodes SET state=(SELECT @rid FROM States WHERE >>> id=$parent.$record.state_id); >>> >>> I never tried with UPDATE but should work. >>> >>> Lvc@ >>> >>> >>> >>> On 13 January 2014 18:00, Giraldo Rosales <[email protected]> wrote: >>> >>>> How do we nest queries? Trying to do an update. Have two types of >>>> documents, zip codes and states. There are two fields in particular in the >>>> zip code document: "state_id" (String) and "state" (Link). Would like to >>>> add SELECT the "id" field in the state documents and insert the RID if it >>>> matches. >>>> >>>> I would have thought something like: >>>> UPDATE Zipcodes SET state=(SELECT @rid FROM States WHERE >>>> id=Zipcodes.state_id); >>>> >>>> Where Zipcodes.state_id is a field in Zipcodes and would match the >>>> field, States.id. It would then select the RID in States and insert it in >>>> the ZIpcodes.state field (Link). >>>> >>>> In MySQL examples are here: >>>> http://dev.mysql.com/doc/refman/5.0/en/subqueries.html >>>> >>>> How is this done in OrientDB? Saw something with the LET command but >>>> doesn't seem to work with updates. >>>> >>>> Thanks! >>>> >>>> -- >>>> >>>> --- >>>> 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/groups/opt_out. >>>> >>> >>> -- --- 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/groups/opt_out.
