Skip to main content

Execute Non Query

Executes a MySQL SQL command that does not return data (INSERT, UPDATE, DELETE, etc.).

Common Properties​

  • Name - The custom name of the node.
  • Color - The custom color of the node.
  • Delay Before (sec) - Waits in seconds before executing the node.
  • Delay After (sec) - Waits in seconds after executing node.
  • Continue On Error - Automation will continue regardless of any error. The default value is false.
info

If the ContinueOnError property is true, no error is caught when the project is executed, even if a Catch node is used.

Inputs​

  • Connection Id - The unique identifier of the database connection to use (optional if credentials provided directly).
  • Transaction Id - The unique identifier of a transaction (optional). Use when the command is part of a database transaction.
  • SQL Query - The SQL command to execute (INSERT, UPDATE, DELETE, CREATE, ALTER, etc.). Supports mustache template variables (e.g., {{city}}, {{userId}}).

Options​

  • Credentials - MySQL database credentials (optional). Use this if you want to execute the command without a Connect node.
  • Parameters - Additional connection string parameters (optional). Only used when credentials are provided.

Output​

  • None - The node does not return query results, but changes are applied to the database.

How It Works​

The Execute Non Query node executes SQL commands that modify data or database structure. When executed, the node:

  1. Retrieves the database connection (either from connection ID or creates new connection from credentials)
  2. Optionally retrieves the transaction if a transaction ID is provided
  3. Processes mustache template variables in the SQL command using the message context
  4. Decodes any HTML entities in the rendered command
  5. Creates a MySqlCommand with the SQL statement
  6. Executes the command using ExecuteNonQuery
  7. Applies the changes to the database

Requirements​

  • Either: A valid connection ID from Connect node OR valid database credentials
  • Valid SQL command (INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, etc.)
  • If using transactions, a valid transaction ID from Start Transaction node
  • Appropriate database permissions for the operation

Error Handling​

The node will return specific errors in the following cases:

  • ErrInvalidArg - Neither connection ID nor credentials provided
  • ErrNotFound - Connection ID or transaction ID not found
  • ErrRuntime - SQL command execution failed (syntax error, constraint violation, permission denied, etc.)

Usage Examples​

UPDATE Statement​

UPDATE users
SET City = '{{city}}', UpdatedAt = NOW()
WHERE ID = {{userId}}

DELETE Statement​

DELETE FROM orders
WHERE OrderDate < '{{cutoffDate}}'
  AND Status = 'cancelled'

INSERT Statement​

INSERT INTO customers (Name, Email, City, Phone, CreatedAt)
VALUES ('{{name}}', '{{email}}', '{{city}}', '{{phone}}', NOW())

Multiple INSERT​

INSERT INTO products (Name, Category, Price, Stock)
VALUES
  ('{{product1Name}}', '{{category}}', {{price1}}, {{stock1}}),
  ('{{product2Name}}', '{{category}}', {{price2}}, {{stock2}}),
  ('{{product3Name}}', '{{category}}', {{price3}}, {{stock3}})

UPDATE with JOIN​

UPDATE orders o
JOIN customers c ON o.CustomerID = c.CustomerID
SET o.CustomerEmail = c.Email
WHERE c.EmailUpdated = 1
  AND o.OrderDate > '{{startDate}}'

Conditional UPDATE​

UPDATE inventory
SET Stock = Stock - {{quantity}},
    LastUpdated = NOW()
WHERE ProductID = {{productId}}
  AND Stock >= {{quantity}}

DELETE with Subquery​

DELETE FROM logs
WHERE LogID IN (
  SELECT LogID FROM (
    SELECT LogID
    FROM logs
    WHERE CreatedAt < DATE_SUB(NOW(), INTERVAL {{days}} DAY)
    ORDER BY CreatedAt
    LIMIT {{batchSize}}
  ) AS old_logs
)

Common Use Cases​

  1. Data Updates - Update customer information, order status, inventory quantities
  2. Data Deletion - Remove old logs, cancelled orders, expired records
  3. Data Insertion - Add new customers, orders, products
  4. Bulk Operations - Update or delete multiple records matching criteria
  5. Data Cleanup - Archive old data, remove duplicates, normalize data
  6. Status Changes - Mark orders as shipped, users as inactive, tasks as complete
  7. Incremental Updates - Increment counters, update timestamps, adjust quantities

DDL Operations​

CREATE TABLE​

CREATE TABLE IF NOT EXISTS {{tableName}} (
  ID INT AUTO_INCREMENT PRIMARY KEY,
  Name VARCHAR(255) NOT NULL,
  Email VARCHAR(255) UNIQUE,
  CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)

ALTER TABLE​

ALTER TABLE {{tableName}}
ADD COLUMN {{columnName}} {{dataType}}

DROP TABLE​

DROP TABLE IF EXISTS {{tableName}}

CREATE INDEX​

CREATE INDEX idx_{{indexName}}
ON {{tableName}} ({{columnName}})

Working with Template Variables​

Message Context Example​

{
  "userId": 1001,
  "city": "San Francisco",
  "status": "active",
  "quantity": 5,
  "productId": 2501,
  "email": "user@example.com",
  "name": "John Doe"
}

SQL with Variables​

UPDATE users
SET
  City = '{{city}}',
  Status = '{{status}}',
  UpdatedAt = NOW()
WHERE UserID = {{userId}}

String Escaping​

Mustache automatically handles values, but be careful with:

-- Good: Use template variables for values
WHERE Name = '{{name}}'

-- Avoid: SQL injection risk if building queries manually
-- Use template variables instead of string concatenation

Tips for Effective Use​

  • Template Variables - Use mustache syntax {{variable}} for dynamic values
  • SQL Editor - The node provides syntax highlighting for SQL statements
  • Transactions - Use transactions for multiple related updates to ensure data consistency
  • Affected Rows - The node executes the command but doesn't return the count of affected rows
  • Batch Operations - For large datasets, process in batches to avoid timeouts
  • Direct Credentials Mode - Use the optional Credentials field to execute without a Connect node
  • Error Handling - Wrap critical updates in Try-Catch blocks
  • Testing - Test UPDATE/DELETE statements as SELECT first to verify the WHERE clause

Best Practices​

Always Use WHERE Clause​

-- Dangerous: Updates ALL rows
UPDATE products SET Price = Price * 1.1

-- Safe: Updates specific rows
UPDATE products
SET Price = Price * 1.1
WHERE Category = '{{category}}'

Test Before Executing​

Convert UPDATE/DELETE to SELECT to verify:

-- Test first
SELECT * FROM orders
WHERE Status = 'pending' AND OrderDate < '{{cutoffDate}}'

-- Then execute
DELETE FROM orders
WHERE Status = 'pending' AND OrderDate < '{{cutoffDate}}'
Connect
  ↓
Start Transaction
  ↓
Non Query 1 (update inventory)
  ↓
Non Query 2 (update order status)
  ↓
Non Query 3 (log transaction)
  ↓
Commit (or Rollback on error)
  ↓
Disconnect

Batch Processing​

For large updates:

// Process in batches of 1000
const batchSize = 1000;
const offset = $item.batchNumber * batchSize;
UPDATE products
SET Processed = 1
WHERE Processed = 0
LIMIT {{batchSize}}

Transaction Example​

Account Transfer (Atomic Operation)​

Connect
  ↓
Start Transaction
  ↓
Non Query 1: Deduct from Account A
UPDATE accounts
SET Balance = Balance - {{amount}}
WHERE AccountID = {{fromAccount}}
  AND Balance >= {{amount}}
  ↓
Non Query 2: Add to Account B
UPDATE accounts
SET Balance = Balance + {{amount}}
WHERE AccountID = {{toAccount}}
  ↓
Non Query 3: Log Transaction
INSERT INTO transactions (FromAccount, ToAccount, Amount, TransactionDate)
VALUES ({{fromAccount}}, {{toAccount}}, {{amount}}, NOW())
  ↓
Commit
  ↓
Disconnect

Common Errors and Solutions​

SQL Command Execution Failed​

Error: ErrRuntime: SQL command execution failed

Common Causes:

  • Syntax error in SQL
  • Table or column doesn't exist
  • Constraint violation (UNIQUE, FOREIGN KEY, NOT NULL)
  • Permission denied
  • Invalid data type

Solutions:

  • Test SQL in MySQL client first
  • Verify table and column names
  • Check constraint requirements
  • Ensure user has appropriate permissions (UPDATE, DELETE, INSERT)
  • Validate data types match column definitions

Duplicate Entry Error​

Error: Duplicate entry 'value' for key 'unique_constraint'

Solutions:

  • Use INSERT IGNORE to skip duplicates
  • Use INSERT ... ON DUPLICATE KEY UPDATE to update on conflict
  • Check for existing records before inserting
  • Use UPDATE instead of INSERT if record might exist

Foreign Key Constraint Violation​

Error: Cannot add or update a child row: foreign key constraint fails

Solutions:

  • Verify the referenced record exists in the parent table
  • Insert parent record first, then child
  • Use transactions to ensure referential integrity
  • Check foreign key relationships

Deadlock Detected​

Error: Deadlock found when trying to get lock

Solutions:

  • Use transactions with proper isolation levels
  • Keep transactions short
  • Access tables in consistent order
  • Retry the operation after a delay

Advanced Examples​

Upsert (Insert or Update)​

INSERT INTO users (UserID, Name, Email, City)
VALUES ({{userId}}, '{{name}}', '{{email}}', '{{city}}')
ON DUPLICATE KEY UPDATE
  Name = VALUES(Name),
  Email = VALUES(Email),
  City = VALUES(City),
  UpdatedAt = NOW()

Conditional Insert​

INSERT INTO inventory (ProductID, Quantity)
SELECT {{productId}}, {{quantity}}
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM inventory
  WHERE ProductID = {{productId}}
)

Bulk Update with CASE​

UPDATE products
SET Price = CASE
  WHEN Category = 'Electronics' THEN Price * 1.15
  WHEN Category = 'Clothing' THEN Price * 1.10
  WHEN Category = 'Food' THEN Price * 1.05
  ELSE Price
END
WHERE PriceUpdateDate < '{{lastUpdateDate}}'

Archive Old Records​

-- Insert into archive table
INSERT INTO orders_archive
SELECT * FROM orders
WHERE OrderDate < DATE_SUB(NOW(), INTERVAL {{months}} MONTH)
  AND Status = 'completed'

-- Then delete from main table
DELETE FROM orders
WHERE OrderDate < DATE_SUB(NOW(), INTERVAL {{months}} MONTH)
  AND Status = 'completed'

Performance Considerations​

  • Indexes - Ensure WHERE clause columns are indexed
  • Batch Size - Limit large operations using LIMIT
  • Locks - UPDATE/DELETE acquire row locks; keep transactions short
  • Cascades - Be aware of foreign key cascades affecting performance
  • Triggers - Know if triggers will fire on UPDATE/INSERT/DELETE

See Also​