Help with MERGE: Create relationship if there's no previous relationship

[mrksph]

Hi there,

I'm working on our Data Reload cypher script.

We've already moved from DETACH DELETEing the whole database and then loading everything from scratch to use MERGE clause. This helps preventing the machine run out of memory which is good.

Now I've detected that sometimes while using MERGE, it will create a new relationship (as expected) but the previous one won't be updated bringing us to a situation where a node has two active parents (big No No for us).

Here's an example.

This is a small part of our cypher script where the new relationships are being created.

USING PERIODIC COMMIT 500
LOAD CSV FROM 'file:///DATA_FILE.txt' AS row FIELDTERMINATOR '~'
WITH
  row[1] as Code,
  row[11] AS Parent
MATCH (child:Client {code: Code})
MATCH (parent:Client {code: Parent})
MERGE (parent)-[r:PARENT_OF]->(child)
  ON CREATE
    SET
    r.isActive= TRUE,
    r.startDate= apoc.temporal.format(datetime(), 'iso_instant'),
    r.updatedBy= 'System';

As I said before, it is creating new relationships as expected but I want to also update the previous relationship, if there's any, updating a property to set it to false.

This is what I've tried so far:

USING PERIODIC COMMIT 500
LOAD CSV FROM 'file:///DATA_FILE.txt' AS row FIELDTERMINATOR '~'
WITH
  row[1] as Code,
  row[11] AS Parent
OPTIONAL MATCH (child:Client {code: Code})<-[oldRel:PARENT_OF]-(oldParent:Client)
MATCH (newParent:Client {code: Parent})
MERGE (newParent)-[newRel:PARENT_OF]->(child)
  ON CREATE
    SET
    oldRel.isActive = FALSE,
    oldRel.endDate = apoc.temporal.format(datetime(), 'iso_instant'),
    oldRel.updatedBy = 'System',
    newRel.isActive= TRUE,
    newRel.startDate= apoc.temporal.format(datetime(), 'iso_instant'),
    newRel.updatedBy= 'System';

Does this makes any sense?

[ameyasoft]

Try this:

USING PERIODIC COMMIT 500
LOAD CSV FROM 'file:///DATA_FILE.txt' AS row FIELDTERMINATOR '~'
WITH
  row[1] as Code,
  row[11] AS Parent
  
OPTIONAL MATCH (child:Client {code: Code})<-[oldRel:PARENT_OF]-(oldParent:Client)
SET oldRel.isActive = FALSE, 
    oldRel.endDate = apoc.temporal.format(datetime(), 'iso_instant'),
    oldRel.updatedBy = 'System'


MATCH (newParent:Client {code: Parent})
WITH child, newParent

MERGE (newParent)-[newRel:PARENT_OF]->(child)
  ON CREATE
    SET
    newRel.isActive= TRUE,
    newRel.startDate= apoc.temporal.format(datetime(), 'iso_instant'),
    newRel.updatedBy= 'System';

[mrksph]

Hi @ameyasoft

I've just tried your snippet and got an error.

It was missing a WITH clause before

WITH Code, Parent, child, oldRel, oldParent
MATCH (newParent:Client {code: Parent})

I think I only need to pass Code but just in case I passed everything.

I'm still getting errors while executing this script but I think I'm almost there.

Thanks

USING PERIODIC COMMIT 500
LOAD CSV FROM 'file:///DATA_FILE.txt' AS row FIELDTERMINATOR '~'
WITH
	row[1] as Code,
	row[11] AS Parent
MATCH (child:Client {code: Code})
OPTIONAL MATCH (child)<-[oldRel:PARENT_OF]-(oldParent:Client)
	SET
	oldRel.isActive = FALSE,
	oldRel.endDate = apoc.temporal.format(datetime(), 'iso_instant'),
	oldRel.updatedBy = 'System'

WITH Code, Parent, child, oldRel, oldParent
MATCH (newParent:Client {code: Parent})
WITH child, newParent

MERGE (newParent)-[newRel:PARENT_OF]->(child)
ON CREATE
	SET
	newRel.isActive= TRUE,
	newRel.startDate= apoc.temporal.format(datetime(), 'iso_instant'),
	newRel.updatedBy= 'System';

[mrksph · Accepted solution]

Hey there,

After a few tries I decided to go on with this version of the snippet

Tell me what you think

Note the WHERE clause. If not used, it would be setting isActive to false for every record in the file, even though the relationship is the one to be created later on.

USING PERIODIC COMMIT 500
LOAD CSV FROM 'file:///DATA_FILE.txt' AS row FIELDTERMINATOR '~'
WITH
  row[1] as Code,
  row[11] AS Parent
MATCH (child:Client {code: Code})
MATCH (child)<-[oldRel:PARENT_OF]-(oldParent:Client)
WHERE oldParent.code <> Parent
  SET
  oldRel.isActive = FALSE,
  oldRel.endDate = apoc.temporal.format(datetime(), 'iso_instant'),
  oldRel.updatedBy = 'System'

WITH Code, Parent, child, oldRel, oldParent
MATCH (newParent:Client {code: Parent})
WITH child, newParent

MERGE (newParent)-[newRel:PARENT_OF]->(child)
  ON CREATE
    SET
    newRel.isActive= TRUE,
    newRel.startDate= apoc.temporal.format(datetime(), 'iso_instant'),
    newRel.updatedBy= 'System';

[ameyasoft]

Yes, this should work. I knew that it would set flag to false in many places, but I do not have a clear understanding of your data model.