How to Perform DELETE and DETACH DELETE in Apache AGE
Tags: apacheage, postgres, database
Published: 2023-05-18 16:23:24

Let's explore two important concepts in Apache AGE, the `DELETE` and the `DETACH DELETE` clause. These operations will allow you to remove nodes, edges or paths from the graph database easily and effectively.

---

### DELETE
The `DELETE` clause is used to delete the nodes, edges or path from a graph database. You cannot delete a node with edges using the `DELETE` clause.
In order for you to delete a node using the `DELETE` clause you also have to delete the associative edges that are connected to the node. You can either explicitly delete the vertices, or use the `DETACH DELETE` clause.
Here is the syntax for the `DELETE` operation.
```
demo=# SELECT * FROM cypher('demo', $$ CREATE(p:Person {age: 2, roll: 25}) RETURN p $$)
demo-# as (p agtype);
                                             p
-------------------------------------------------------------------------------------------
 {"id": 1407374883553285, "label": "Person", "properties": {"age": 2, "roll": 25}}::vertex
(1 row)
```
We have just created a node with properties.

If we want to delete this node, we could perform the query;
```
demo=# SELECT * FROM cypher('demo', $$ MATCH (n:Person) DELETE n $$) as (n agtype);
 n
---
(0 rows)
```
And the node will be deleted.

---

### DETACH DELETE
The `DETACH DELETE` clause is similar to the `DELETE` clause, but with a key difference. The `DETACH DELETE` clause is used to delete nodes alongside their associative edges.

Let's create vertices under the label `nodes` with connecting edges in the `demo` graph.

```
demo=# SELECT * FROM cypher('demo', $$ CREATE (a:node {name: 'Sam'}),
(b:node {name: 'Peter'}), (c:node {name: 'Ken'}), (d:node {name: 'Simon'}),
(a)-[:Knows]->(b),
(c)-[:Works_with]->(d)
$$) as (a agtype);
 a
---
(0 rows)

demo=# SELECT * FROM cypher('demo', $$ MATCH(n) RETURN n $$) as (n agtype);
                                             n
-------------------------------------------------------------------------------------------
 {"id": 844424930131972, "label": "node", "properties": {"name": "Sam"}}::vertex
 {"id": 844424930131973, "label": "node", "properties": {"name": "Peter"}}::vertex
 {"id": 844424930131974, "label": "node", "properties": {"name": "Ken"}}::vertex
 {"id": 844424930131975, "label": "node", "properties": {"name": "Simon"}}::vertex
(4 rows)
```
And now, we want to delete all the nodes and along with its connecting edges.

If we use the `DELETE` clause we will get an error.
```
demo=# SELECT * FROM cypher('demo', $$ MATCH (n:node) DELETE n $$) as (n agtype);
ERROR:  Cannot delete vertex n, because it still has edges attached. To delete this vertex, you must first delete the attached edges.
```
According to the error message the nodes have associative edges, So we use `DETACH DELETE`.

```
demo=# SELECT * FROM cypher('demo', $$ MATCH (n:node) DETACH DELETE n $$) as (n agtype);
 n
---
(0 rows)
```

---

### Deleting edges using the `DELETE` clause
If you want to delete specific edges from your nodes, you can use the `MATCH` clause to find your edges, then add the variable to the `DELETE`.
```
demo=# SELECT * FROM cypher('demo', $$ MATCH (a {name: 'Sam'})-[r:Knows]->(b {name: 'Peter'}) DELETE r
$$) as (a agtype);
 a
---
(0 rows)
```
And if you go ahead to check the edge (relationship)...
```
demo=# SELECT * FROM cypher('demo', $$ MATCH (a {name: 'Sam'})-[r]->(b {name: 'Peter'}) RETURN type(r)
$$) as (relationship agtype);
 relationship
--------------
(0 rows)
```
_Sam doesn't know Peter anymore..._

---

Also in Apache AGE, you can return the vertices that has been deleted.
```
demo=# SELECT * FROM cypher('demo', $$ MATCH (p:Person) DELETE p
 RETURN p $$) as (p agtype);
                                             p
-------------------------------------------------------------------------------------------
 {"id": 1407374883553285, "label": "Person", "properties": {"age": 2, "roll": 25}}::vertex
(1 row)
```

And even edges (relationships) that has been deleted;
```
demo=# SELECT * FROM cypher('demo', $$ MATCH (a {name: 'Ken'})-[r:Works_with]->(b {name: 'Simon'}) DELETE r
 RETURN type(r) $$) as (a agtype);
      a
--------------
 "Works_with"
(1 row)
```

---

###Conclusion
The Delete and Detach Delete operations in Apache AGE provide powerful capabilities for managing and manipulating graph data. While Delete operation removes nodes, edges, or paths, The Detach Delete operation allows you to remove nodes alongside its associative edges. Understanding these operations and their appropriate use cases can help you effectively maintain and analyze your graph database.

###Resources
- [Apache AGE Documentation](https://age.apache.org/age-manual/master/clauses/delete.html)
- [Stackoverflow question❓](https://stackoverflow.com/questions/33139903/detach-delete-neo4j-2-3-x-cypher#:~:text=If%20you%20are%20coming%20here,if%20it%20has%20any%20relationships.)
- Visit Apache AGE Website: https://age.apache.org/
- Visit Apache AGE GitHub: https://github.com/apache/age
- Visit Apache AGE Viewer GitHub: https://github.com/apache/age-viewer