Updating and Deleting Data in SQL

Hello! Welcome back to our module on "Working with Databases."

In the last lesson, you successfully learned how to perform Create and Read operations, the 'C' and 'R' in the classic CRUD acronym. You built a workflow to insert new contact data into your database and then read it back. This forms the backbone of many data-driven automations.

Today, we'll complete the set by mastering the 'U' and 'D': Update and Delete operations. By the end of this lesson, you will be able to build n8n workflows that can modify existing records and remove data from your database tables. These skills are essential for tasks like synchronizing data between systems, managing the state of your application data, or performing automated cleanup.

1. Modifying Existing Records with the Update Operation

The Update operation allows you to change the data in one or more existing rows of a table. This is fundamental for keeping your data current. For example, you might update a user's profile, change an order's status from 'processing' to 'shipped', or mark a task as complete.

In n8n, the database nodes provide a dedicated Update operation that simplifies this process. You specify which rows to change and what new data to apply.

As the documentation explains, you have two primary concerns when configuring an update:

1. Identifying the rows to update: This is typically done by matching values in one or more key columns (like an id or a unique email).
2. Providing the new values: You need to supply the data for the columns you wish to change.

Hands-On Practice: Updating a Contact

Let's modify the contacts table you created in the previous lesson. We'll build a workflow to change the email address for the contact named 'Alice'.

Workflow Steps:

1. Start with a Set node: This node will define both the identifier for the row we want to change and the new data.

   • Add a Set node.
   • Set Mode to Append.
   • Add two values:
     • Name: name, Value: Alice (This will identify the row).
     • Name: email, Value: alice.smith@example.com (This is the new email).

2. Add and Configure the Database Node:

   • Add your database node (e.g., Postgres or MySQL).
   • Select your credentials.
   • Set Operation to Update.
   • Select the Table (contacts).
   • Under Match On Columns, enter name. This tells n8n to find the row(s) where the name column matches the name field from our input data (Alice).
   • Under Update Columns, enter email. This tells n8n to take the email field from our input and use it to update the email column in the database.

   
   This image shows a more advanced but common pattern. An IF node checks for a condition (e.g., does the record exist?) and then routes the workflow to either a Postgres Update node or a Postgres Insert node. This is a classic "upsert" (update or insert) logic.

3. Execute the workflow. The node's output should indicate that 1 row was affected. You can verify this by running the SELECT query from the previous lesson to see the updated email address.

2. Removing Data with the Delete Operation

The Delete operation is used to remove rows from a table. In n8n, this operation is designed with safeguards in mind, offering different levels of deletion, from removing specific rows to clearing an entire table.

For most automation tasks, you will use the Delete command with one or more conditions to target specific rows.

Hands-On Practice: Deleting a Contact

Now, let's create a workflow to remove the contact we've been working with. We will target the row using the email address.

Workflow Steps:

1. Start with a Set node: This node will define the identifier for the row to be deleted.

   • Add a Set node.
   • Add one value:
     • Name: email, Value: alice.smith@example.com

2. Add and Configure the Database Node:

   • Add your database node.
   • Select your credentials.
   • Set Operation to Delete.
   • Set the Command to Delete.
   • Select the Table (contacts).
   • Under Select Rows, you will define the condition.
     • Click Add Condition.
     • Set Column to email.
     • Set Operator to =.
     • For the Value, use an expression to get the email from the Set node: {{ $json.email }}.

3. Execute the workflow. The output should again show 1 affected row. If you query your contacts table now, the record for Alice will be gone.

3. A Critical Note on Security: Parameterized Queries

When you configure Update and Delete operations, you are defining the WHERE clause of a SQL statement. In our hands-on examples, we used n8n's UI, which handles data safely.

However, if you were to use the Execute Query operation to write a raw UPDATE or DELETE statement, it's vital that you do not construct the query by simply inserting variables into a string, like this:

UPDATE contacts SET email = 'new@email.com' WHERE name = '{{ $json.name }}' ← DANGEROUS

This practice opens you up to SQL Injection, a severe security vulnerability. An attacker could provide a malicious value for name (e.g., 'Alice'; DROP TABLE users;--) that could alter your query and damage your database.

The correct and safe method is to use parameterized queries. You use placeholders in your query string and then provide the values separately. The database driver ensures the values are treated as data, not as executable code.

We will cover parameterized queries in great detail in our next lesson, but it is crucial to be aware of the concept now as you work with operations that modify or delete data.

Conclusion

Congratulations! You now have full CRUD (Create, Read, Update, Delete) capabilities in your n8n toolkit for SQL databases. You can manage the entire lifecycle of your data directly within your automation workflows.

Key Takeaways:

• Use the Update operation to modify existing rows by specifying which rows to match and which columns to change.
• Use the Delete operation to remove data. Be mindful of the difference between Delete (conditional), Truncate (all rows), and Drop (entire table).
• Always be conscious of security. When using Execute Query for UPDATE or DELETE, you must use parameterized queries to prevent SQL injection.

In our next lesson, we will dive deep into using parameterized queries. You'll learn how to write secure, dynamic SQL statements to handle more complex scenarios safely, a non-negotiable skill for any production-level workflow.