Orientdb: UPSERT to update one property, or insert document with multiple properties values

[rdelangh]

OrientDB Version, operating system, or hardware.

• v2.2.10

  Operating System

• [x] Linux

  Expected behavior and actual behavior

Not sure about the documentation for UPSERT actions, and did not find any equivalent examples on the web, for what we try to achieve:

• if a document for a certain key property exists already in the class, UPDATE one (or at least not all) of its properties value
• otherwise INSERT a new document, which consists of multiple properties, each of which must be given its value with the statement.

I found the "UPDATE... UPSERT" statement in the OrientDB docs, but it does not say me how to

• specify that only 1 single property must be updated in case the document exists already, and in the same statement
• specify all the properties and their values for the case an INSERT must be done if the document does not yet exist

or
IF DOCUMENT EXISTS (keyvalue) THEN
UPDATE property1 = value1
ELSE
INSERT (property1, property2, ..., propertyN) VALUES (value1, value2, ..., valueN)

If possible, give me hint or answer in SQL or in JSON notation.

many thanks in advance!

[smolinari]

Did you see this? http://orientdb.com/docs/2.2/SQL-Update.html#limitations-of-the-upsert-clause

Scott

[rdelangh]

@smolinari
yeah I read that, but there seems to be none relevance to what I am posting as a case: that documentation only mentions that a UNIQUE index must be used to identify the record in the class.
My question is not about the lookup method to find some record, but rather about how to specify that for an UPDATE only one single property of the existing document record must get a new value, where for an INSERT there is a value for all the properties of the new document record.

[smolinari]

Ah, ok. Got it.

My guess is, you would have to list off all of the properties you want set in the new document, which should be data already in any old document, if it exists. So, you wouldn't be changing anything in an old document, other than properties that actually need changing.

Scott

[rdelangh]

@smolinari but that would then imply that I must first do a SELECT to get all the properties of the document (if it exists); so actually 2 actions (SELECT, then UPDATE-UPSERT) instead of one single (UPDATE-UPSERT).
That's not very efficient for zillions of records ...

[smolinari]

I would certainly hope you know what the properties are (what the schema is) in your code to begin with.

Maybe if you could explain your use case better, it might make more sense. What do you want to do exactly?

Scott

[rdelangh]

@smolinari sure, I know the schema and the names of the properties, but not the old values of each and every record (document) in the class.
So if I want to make sure that a particular record, if it exists already, gets an updated value on one of its properties (say property named "prop1", would get new value "new_value1") but all its other properties remain unchanged, or otherwise (that record does not yet exist) I want to insert a new one with values for all of its properties.
In 'traditional' databases, this is done with a MERGE sql statement :
MERGE on tablename a ( SELECT ... ) b
ON ( WHEN MATCHED // record exists already
THEN UPDATE prop1 = value1
ELSE // record does not yet exist
INSERT (prop1, prop2, ..., propN) VALUES (value1, value2, ...., valueN)

Please see my original post at the top of this issue. I hope that it is clear.

[smolinari]

Ahhh! That is not a use case for INSERT with UPSERT and I don't think ODB has an equivelant command like the RDBMS "MERGE" command you're looking for.

Anyone with more experience, please do chime in, if I am wrong.

Scott

[luigidellaquila]

Hi @rdelangh

What you are looking for is not currently supported in OrientDB SQL.
The UPSERT statement works as follows:

INSERT INTO <ClassName>
SET field1=val1, field2=val2...
UPSERT
where field3=val3 and val4=val4...

If a record exists with field3 and field4 then it's updated with ALL the values of field1, field2, otherwise it's created, so there is no distinction between fields that are set with insert and update (eg. you cannot specify that with INSERT you have to set field1, but not with UPDATE).

I'm flagging this as an enhancement, probably it will be addressed in next releases

Thanks

Luigi

[rdelangh]

@luigidellaquila
hi Luigi, thx for the info, awaiting eagerly to see this very handy functionality being added into ODB ;-)

[rdelangh]

@luigidellaquila
Luigi, can you please confirm already in which release this extra functionality will be included ?

[luigidellaquila]

Hi @rdelangh

For now it's scheduled for 3.0

Thanks

Luigi

[smolinari]

@luigidellaquila - would this issue then need the 3.0 milestone flag?

Scott

[luigidellaquila]

Thanks @smolinari , just flagged ;-)

Luigi

[lokeshpkumar]

Is there any update on this enhancement?
In what version of OrientDB will this be available?