How to delete a document when only RID is known, not class?

[rdelangh]

OrientDB Version: 2.2.22

Java Version: n/a

Expected behavior

I use scripts to perform logic on some documents. In such a script, via complex searching, I finally got a RID of some document. That RID is then stored in a variable "$myRID" in the script.
Under some criteria, it happens that I should delete that document. But I do not want to repeat that complex searching for ultimately DELETEing it (might take extra time, and makes the script unnecessary long), while I have already the RID in this variable "$myRID" in the script.

Note: indexes exist, so the DELETE of this document should also update any such indexes. For example, I was informed that a TRUNCATE with a RID would by design NOT update any indexes, so the indexes become inconsistent with the documents in the class.

-> is there no SQL syntax supported in the style of "DELETE FROM $myRID" ?

[luigidellaquila]

Hi @rdelangh

DELETE FROM $myRID will try to search $myRID class, so it won't work.

If you have the RID (instead of a variable) the DELETE will work fine, eg. DELETE FROM #12:0

The only work-around you have is following:

LET $a = SELECT FROM YourClass;
DELETE FROM (SELECT expand($a));

I hope it helps

Thanks

Luigi

[rdelangh]

@luigidellaquila
sorry but this syntax does not delete the record which is held in the variable.
-> can you check please?

orientdb {db=mobile}> script sql    [Started multi-line command. Type just 'end' to finish and execute]                                                  
orientdb {db=mobile}> let rowsInserted = INSERT INTO class1 return @RID from select  from class2 WHERE somecriteria ; if ($rowsInserted.size() > 0) { select $rowsInserted ;}
orientdb {db=mobile}> end

+----+-------------+
|#   |$rowsInserted|
+----+-------------+
|0   |[#404:0]     |
+----+-------------+
Server side script executed in 0.003000 sec(s). Returned 1 records

but using the content of that variable (RID) to delete such record(s), is not working, altough the syntax is accepted:

orientdb {db=mobile}> script sql     [Started multi-line command. Type just 'end' to finish and execute]
orientdb {db=mobile}> let rowsInserted = INSERT INTO class1 return @RID from select  from class2 WHERE somecriteria ; if ($rowsInserted.size() > 0) { delete from (select expand($rowsInserted)) ;}
orientdb {db=mobile}> end

Server side script executed in 0.004000 sec(s). Value returned is: 1
orientdb {db=mobile}> select count(*) FROM class2 WHERE somecriteria

+----+-----+
|#   |count|
+----+-----+
|0   |1    |
+----+-----+

1 item(s) found. Query executed in 0.002 sec(s).

(in which "somecriteria" is each time exactly the same WHERE-clause)
-> so the delete from "class2" is not happening, the record is still there. The INSERT is happening, that new record in "class1" is indeed created.

[luigidellaquila]

Hi @rdelangh

You are doing the delete on the newly created records (class1), then you are doing the select on class2, this is why you are still seeing the old records.

If you want to delete the old records you have to write the script as follows:

let $oldRows = select from class2 WHERE name = 'a';
INSERT INTO class1 from select expand($oldRows); 
if ($oldRows.size() > 0) { 
	delete from (select expand($oldRows));
}

Please consider that in v 2.2.22 the INSERT from SELECT expand() throws a parsing error. I just fixed this problem and the fix is already in 2.2.x branch, it will be released with 2.2.23

Thanks

Luigi

[rdelangh]

hi @luigidellaquila
sorry but that syntax gives an error:

orientdb {db=mobile}> script sql
[Started multi-line command. Type just 'end' to finish and execute]
orientdb {db=mobile}> let rowsToInsert = select from class2 WHERE mycriteria ;
orientdb {db=mobile}> INSERT INTO class1 from select expand($rowsToInsert) ;
orientdb {db=mobile}> if ($rowsToInsert.size() > 0) {
orientdb {db=mobile}> DELETE from (select expand($rowsToInsert)) ;
orientdb {db=mobile}> }
orientdb {db=mobile}> end

Error: com.orientechnologies.orient.core.exception.OCommandExecutionException: Invalid script:Encountered " <INSERT> "INSERT "" at line 1, column 178.
Was expecting one of:
    <IF> ...
    ";" ...

        DB name="mobile"
        DB name="mobile"

[luigidellaquila]

Hi @rdelangh

Did you get latest snapshot? As I wrote above, there is a fix on 2.2.x branch that is needed to make it work...

Thanks

Luigi

[rdelangh]

latest 2.2.23 snapshot dates from June-28, which is earlier than when you made a comment about your fix. Which snapshot will contain your fix?

[luigidellaquila]

Hi @rdelangh

we just generated a new snapshot, you can find it here

https://oss.sonatype.org/content/repositories/snapshots/com/orientechnologies/orientdb-community/2.2.23-SNAPSHOT/

Thanks

Luigi