MySQL Update

Overview

This Snap executes a SQL update with the given properties. Keys from the document will be used as the columns to update and their values will be the updated value in the column.

Prerequisites

A valid account with UPDATE privileges on the target MySQL database table.

Snap views

Type Description Examples of upstream and downstream Snaps
Input Each input document provides the values to update and the conditions for selecting rows to update.
Output Each output document contains the updated row data along with the number of rows affected by the update operation.
Learn more about Error handling.

Snap settings

Legend:
  • Expression icon (): Allows using pipeline parameters to set field values dynamically (if enabled). SnapLogic Expressions are not supported. If disabled, you can provide a static value.
  • SnapGPT (): Generates SnapLogic Expressions based on natural language using SnapGPT. Learn more.
  • Suggestion icon (): Populates a list of values dynamically based on your Snap configuration. You can select only one attribute at a time using the icon. Type into the field if it supports a comma-separated list of values.
  • Upload : Uploads files. Learn more.
Learn more about the icons in the Snap settings dialog.
Field/Field set Description
Label

String		 Required. The name for the Snap. You can modify this to be more specific, especially if you have more than one of the same Snap in your pipeline.
Schema name

String/Expression/ Suggestion

 The database schema name. Selecting a schema filters the Table name list to show only those tables within the selected schema. The property is suggestable and will retrieve available database schemas during suggest values.

Example: test

Default value: [None]
Table name

String/Expression/ Suggestion

 Required. The name of the table to execute the update operation on.

The values can be passed using the pipeline parameters but not the upstream parameter.

Example: people

Default value: [None]
Update condition

String/Expression

 Required. Specify the SQL WHERE clause of the update statement. You can define specific values or columns to update (Set condition) in the upstream Snap, such as Mapper Snap, and then use the WHERE clause to apply these conditions on the columns sourced from the upstream Snap. For instance, here is a sample of an Update SQL query:
UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

If the Update Condition field is left blank, the condition is applied on all the records of the target table.

In certain scenarios where you want to use specific data from the upstream Snaps, and do not want to change that data in the Update Snap, then you need to place the data in the original structure of the input document. Hence, we recommend that you use the format for Update condition and input data format as shown below:

{
"valueToUpdate" : "true",
"original": {
"col1" : "KEY"
}
}

Refer to the example to understand how to use the Update Condition.

Example:

Without using expressions

Using expressions

  • "EMPNO=$EMPNO and ENAME=$EMPNAME"
  • email = $email
  • emp=$emp
  • "emp='" + $emp + "'"
  • "EMPNO=" + $EMPNO + " and ENAME='" + $EMPNAME+ "'"
CAUTION: Using expressions that join strings together to create SQL queries or conditions has a potential SQL injection risk and is hence unsafe. Ensure that you understand all implications and risks involved before using concatenation of strings with '=' Expression enabled.
Number of retries

Integer

 Specifies the maximum number of attempts to be made to receive a response. The request is terminated if the attempts do not result in a response.

Example: 3

Default value: 0

If the value is larger than 0, the Snap first downloads the target file into a temporary local file. If any error occurs during the download, the Snap waits for the time specified in the Retry interval and attempts to download the file again from the beginning. When the download is successful, the Snap streams the data from the temporary file to the downstream Pipeline. All temporary local files are deleted when they are no longer needed.

Note: Ensure that the local drive has sufficient free disk space to store the temporary local file.
Retry interval (seconds)

Integer

 Specifies the time interval between two successive retry requests. A retry happens only when the previous attempt resulted in an exception.

Example: 10

Default value: 1
Snap execution

String/Suggestion

 Select one of the three modes in which the Snap executes. Available options are:
  • Validate & Execute: Performs limited execution of the Snap, and generates a data preview during Pipeline validation. Subsequently, performs full execution of the Snap (unlimited records) during Pipeline runtime.
  • Execute only: Performs full execution of the Snap during Pipeline execution without generating preview data.
  • Disabled: Disables the Snap and all Snaps that are downstream from it.