Heliont MySQL icon

Heliont MySQL

Heliont wrapper around the MySQL node
Join our community

Actions6

Overview

This node is designed to update rows in a specified database table. It allows users to modify existing records by matching on a specific column and updating one or more columns with new values. This node is useful in scenarios where you need to programmatically update data in a database, such as syncing external data sources, correcting records, or applying batch updates based on incoming workflow data.

Practical examples include:

  • Updating user information in a customer database when new details are received.
  • Changing the status of orders in an e-commerce system based on processing results.
  • Modifying inventory counts after stock audits.

The node supports two modes for specifying update data: automatic mapping from input fields to table columns, or manual definition of each column-value pair to update.

Properties

Name Meaning
Table The target database table to update. Can be selected from a list or specified by name.
Data Mode How to specify the data to update:
- Auto-Map Input Data to Columns: Automatically map input property names to table columns (input field names must match column names).
- Map Each Column Below: Manually define each column and its new value.
Column to Match On The column used to find the row(s) to update. Rows with this column matching the specified value will be updated. This column itself is not changed.
Value of Column to Match On The value to match in the "Column to Match On" to identify which rows to update. Only shown in manual mapping mode.
Values to Send In manual mapping mode, the list of columns and their corresponding new values to set in the matched rows.
Options Additional options for the update operation:
- Connection Timeout (ms)
- Connections Limit
- Query Batching (single query, independent queries per item, or transaction)
- Replace Empty Strings with NULL
- Output Query Execution Details (show detailed query info in output)

Notes

  • When using Auto-Map Input Data to Columns, ensure that the incoming data fields exactly match the column names in the table. An "Edit Fields" node can be used before this node to rename fields if necessary.
  • The "Column to Match On" is critical for identifying which rows to update and should uniquely identify rows to avoid unintended multiple updates.

Output

The node outputs JSON data representing the result of the update operation for each input item. The structure includes:

  • If detailed output is enabled, the output contains debug information about the executed query, including the query string, type, number of rows affected, and raw results.
  • For each input item, the output JSON merges the original input data with any additional result information.
  • The output does not produce binary data.

Output format example (simplified):

{
  "json": {
    "debug": {
      "query": "UPDATE ...",
      "queryType": "UPDATE",
      "numberOfRows": 1,
      "rows": [ /* raw result rows */ ],
      "rawResult": [ /* full raw response */ ]
    },
    /* original input fields */,
    "result": {
      "numberOfRows": 1,
      "results": [ /* updated rows info */ ]
    }
  }
}

If no rows are updated, the output indicates "No Result".

Dependencies

  • Requires connection credentials to the target database (e.g., MySQL).
  • Uses internal methods for loading table and column metadata dynamically.
  • No external API dependencies beyond the database connection.
  • Configuration of connection parameters like timeout and connection limits is supported.

Troubleshooting

  • No rows updated: Ensure the "Column to Match On" and "Value of Column to Match On" correctly identify existing rows. Mismatches here cause zero rows to be updated.
  • Field name mismatches in auto-map mode: Incoming data field names must exactly match the table column names. Use an "Edit Fields" node to rename fields if necessary.
  • Connection errors: Check database credentials, network connectivity, and configured timeouts.
  • Query batching issues: Selecting "transaction" mode requires the database to support transactions; otherwise, errors may occur.
  • Empty strings not replaced: If empty strings should be stored as NULL, enable the "Replace Empty Strings with NULL" option.
  • Performance issues: Setting a very high connection limit can overload the database server.

Links and References

Discussion