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';
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';
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';
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';