--- schema_version: "1" id: 041a3e5efd6bc7050f9cdd036f2a796e13b7a290cec5a7232b0cc03f337b6352 title: Insert, Update, and Delete Data from a Database Examples collection: mulesoft version: current locale: en-us portal_url: https://docs.salesforce.com/mulesoft/current/en-us/db-connector/1.14/database-insert-update-delete.md canonical_url: https://docs.mulesoft.com/db-connector/1.14/database-insert-update-delete source_site: docs.mulesoft.com content_type: documentation content_hash: 1da3001da4c67f85149db27ab830e9c0b778b358259487d5efc166a4ce1a6dab description: MuleSoft Documentation Site release: "1.14" latest: false --- --- product: Database Connector version: 1.14 --- # Insert, Update, and Delete Data from a Database Examples > For the full documentation index, see: https://docs.mulesoft.com/llms.txt Anypoint Connector for Database (Database Connector) supports the **Insert**, **Update**, and **Delete** operations that enable you to manage data from a database. In the following example, you insert, update ,and delete data from a database schema named `Products` that has a table named `electronic`. The table contains columns named `id`, `name`, `description`, `price`, and `discount`. The following SQL statement creates the table: ```sql CREATE TABLE electronic( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), description VARCHAR(255), price SMALLINT, discount TINYINT ) ``` ## Configure the Insert Operation in Studio Given that the table `electronic` is already created and there is a Database Connector connection configuration named `dbConfig`, you can configure the **Insert** operation that inserts a new record in the table: 1. In your Studio flow, select the **Insert** operation. 2. In the connector configuration screen, set the **SQL Query Text** field to the query to execute, for example: ```XML INSERT INTO electronic(name, description, price, discount) VALUES ('Coffee Machine', 'Model: XYZ99. Uses small size capsules.', 120, 5) ``` The `id` parameter is not passed in the query because it is an auto-increment key that is generated automatically by the database. ![The Insert operation configuration](/mulesoft/current/en-us/db-connector/1.14/_images/database-insert-example-1.png) In the **Configuration XML** editor, the `` configuration looks like this: ```xml INSERT INTO electronic(name, description, price, discount) VALUES ('Coffee Machine', 'Model: XYZ99. Uses small size capsules.', 120, 5) ``` ### Configure the Input Parameters Field in the Insert Operation To benefit from the advantages of input parameters, structure the equivalent SQL query as follows: 1. In your Studio flow, select the **Insert** operation. 2. In the connector configuration screen, set the **SQL Query Text** field to the query to execute, for example: ```xml INSERT INTO electronic(name, description, price, discount) VALUES (:name, :description, :price, :discount) ``` 3. In the **Input Parameters** field, set the following parameters: ```xml { name: 'Coffee Machine', description: 'Model:XYZ99. Uses small size capsules.', price: 120, discount: 5 } ``` ![The Input Parameter field configuration](/mulesoft/current/en-us/db-connector/1.14/_images/database-insert-example-2.png) In the **Configuration XML** editor, the `` configuration looks like this: ```xml INSERT INTO electronic(name, description, price, discount) VALUES (:name, :description, :price, :discount) #[{ name: 'Coffee Machine', description: 'Model:XYZ99. Uses small size capsules.', price: 120, discount: 5}] ``` ### Configure the Auto Generate Keys Fields in the Insert Operation The **Insert** operation outputs a `Statement Result` object that contains two fields: an `affectedRows` integer that indicates how many rows were affected by the query, and a `generatedKeys` map that contains the autogenerated keys. In the following example, the output shows that the `generatedKeys` map is empty, although a row is successfully added to the table. The map is empty because this information is disabled by default to avoid overhead. ```json { "affectedRows": 1, "generatedKeys": {} } ``` To activate the information and generate keys, follow the next steps: 1. In your Studio flow, select the **Insert** operation. 2. In the **Advanced** tab, set the **Auto generate keys** field to **True**. ![The Input Parameter field configuration](/mulesoft/current/en-us/db-connector/1.14/_images/database-insert-example-3.png) In the **Configuration XML** editor, the `` configuration looks like this: ```xml ... ``` If the statement generates multiple keys per affected row, you can specify which columns should be returned by setting a list that contains the desired column names, for example: 1. In your Studio flow, select the **Insert** operation. 2. In the **Advanced** tab, set the **Auto generate column names** field to `Expression or Bean reference` to set the field to a DataWeave list with a single string element. 3. Set the expression field to `['id']` to ensure that only the `id` column is returned. ![The Auto generate column names field configuration](/mulesoft/current/en-us/db-connector/1.14/_images/database-insert-example-4.png) In the **Configuration XML** editor, the `` configuration looks like this: ```xml ... ``` ## Configure the Update Operation in Studio Given the `electronic` table previously defined, to update data from the table (for example, setting the discount to 10 percent for all items that have a price value above 100), configure the **Update** operation: 1. In your Studio flow, select the **Update** operation. 2. In the connector configuration screen, set the **SQL Query Text** field to the query to execute, for example, `"UPDATE electronic SET discount = :discount WHERE price > :price"`. 3. Set the **Input parameters** field to the following parameters: ```xml { discount: 10, price: 100 } ``` ![The Update operation configuration](/mulesoft/current/en-us/db-connector/1.14/_images/database-update-example-1.png) In the **Configuration XML** editor, the `` configuration looks like this: ```xml :price"]]> ``` The `` wrapper enables you to use special characters, such as `>` or `"`, in the query. Otherwise, you must use XML-escaped versions of those characters, such as `>` and `"`. ## Configure the Delete Operation in Studio Given the `electronic` table previously defined, to remove the record with `id: 1`, configure the **Delete** operation: 1. In your Studio flow, select the **Update** operation. 2. In the connector configuration screen, set the **SQL Query Text** field to the query to execute, for example, `DELETE FROM electronic WHERE id = :id`. 3. Set the **Input parameters** field to `{id: 1}`. ![The Update operation configuration in Studio](/mulesoft/current/en-us/db-connector/1.14/_images/database-delete-example-1.png) In the **Configuration XML** editor, the `` configuration looks like this: ```xml DELETE FROM electronic WHERE id = :id #[{ id: 1 }] ``` ## Insert Record Mule App Example The following example shows how to configure the **Insert** operation to create a person’s record in a database. 1. In Studio, drag the HTTP **Listener** source to the Studio canvas. 2. Set **Path** to `/person`. 3. Drag the **Insert** operation next to the **Listener** source. 4. Click the plus sign (**+**) next to the **Connector configuration** field to access the global element configuration fields. 5. In the **SQL Query Text** box, declare the following SQL text: ```xml insert into person (id, name, photo, dob) values (:ID,:NAME,:PHOTO,:DOB); ``` 6. In the **Input Parameters** field, specify a list of maps of the person record to create, which contains the parameter names as keys and the value the parameter, and in which every list item represents a row to insert: ```xml {'ID' : message.payload.parts['id'].content , 'NAME': message.payload.parts['name'].content, 'PHOTO' : message.payload.parts['photo'].content, 'DOB' : message.payload.parts['dob'].content} ``` 7. Drag a **Set Payload** component next to the **Insert** operation. 8. Set **Value** to `Person Created`. 9. Save and run your Mule application. In the **Configuration XML** editor, the configuration looks like this: ```xml ``` ## See Also - [Database Connector Examples](https://docs.mulesoft.com/db-connector/1.14/database-connector-examples) - [Salesforce Help](https://help.salesforce.com)