OrientDB - Update with SUBSELECT

3
votes

I want to update some rows of my table basing on other rows of the same table:

I try this:

UPDATE MyTable set myField = 
    (SELECT T1.myField
    FROM MyTable T1
    WHERE T1.id.substring(start,stop) = MyTable.id.substring(start,stop))

But OrientDB throws an error like this:

com.orientechnologies.orient.core.sql.OCommandSQLParsingException: Error on parsing command at position #XXX: Invalid keyword 'T1' Command:

2
orientdb
does Orient need the AS keyword? FROM MyTable AS T1 ? - Brian Dolan
Dear, with or without AS Orient returns an error - Joe Taras
I don't see table alias mentioned at all in the docs. Try w/o the alias if you have a sec, just typing out MyTable. - Brian Dolan
Without alias how can I say to Orient engine how to resolve ambigous field? - Joe Taras
I am going to give this a shot in Orient in the next hour and let you know what I find. But in MySQL for instance, you can do this: DROP TABLE IF EXISTS update_me; CREATE TABLE update_me ( id int, body text, subbody text) ; INSERT INTO update_me (id, body) VALUES (1, "this is my first row") , (2, "this is my second row") ; UPDATE update_me set subbody = substring(body, 11, 19); - Brian Dolan

2 Answers

2
votes

first of all you in OrientDB you can't use Alias on Classes.

In this case you could use $parent.$current in a subquery, something like:

> update MyTable set myField = (
>         select myField
>         from MyTable
>         where myField is null
>         and id.substring(8,13) = $parent.$current.id.substring(8,13) and something else...
>          ) where myField is null and something else...

Be careful to the length of the id...

Best Regards M.

0
votes

This is not a string update, but an integer update in place. Using the provided GratefulDeadDatabase, you can do:

CONNECT remote:localhost/GratefulDeadConcerts;
SELECT performances FROM v;
----+------+------------
#   |@CLASS|performances
----+------+------------
0   |null  |null        
1   |null  |5           
2   |null  |1           
3   |null  |531         
4   |null  |394         
----+------+------------

UPDATE v SET performances = eval('performances + 2') WHERE performances IS NOT NULL;
SELECT performances FROM v;
----+------+------------
#   |@CLASS|performances
----+------+------------
0   |null  |null        
1   |null  |7           
2   |null  |3           
3   |null  |533         
4   |null  |396         
----+------+------------

So the update works on the data in place. I'm fairly new to OrientDB so maybe an expert can tell me if I just did something horribly horribly wrong.

UPDATE

Notice that in your example you are updating the table with values from the same table. That is, from MyTable into MyTable (unless I misunderstood your query) and even within the same row. You can use criteria on the WHERE clause to only update rows of interest. In my example, that was

WHERE performances IS NOT NULL