UPDATE and INSERT seems to be working fine with sub-queries now. Not sure if it was due to restarting the server or updating to the latest on Git.
SELECT still does not work but able to use the dot notation: SELECT FROM Users WHERE location.id = "1"; On Tuesday, January 14, 2014 10:12:06 AM UTC-5, Giraldo Rosales wrote: > > 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.
