Mastering Data Manipulation in SQL: INSERT, UPDATE, DELETE, and MERGE — Codes With Pankaj

In the realm of relational databases, data manipulation is a fundamental skill. SQL (Structured Query Language) provides a powerful set of tools for these operations, allowing you to add new data, modify existing data, remove unwanted records, and even orchestrate complex actions that combine these operations. In this blog, we’ll explore these essential data manipulation operations with clear examples.

SQL INSERT Adds new data.

An SQL INSERT query is used to insert new records or rows into a database table. Here’s a basic example of an SQL INSERT query:

Suppose you have a table named “employees” with the following structure:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    job_title VARCHAR(100),
    salary DECIMAL(10, 2)
);

You can use an INSERT query to add a new employee to the “employees” table:

INSERT INTO employees (employee_id, first_name, last_name, job_title, salary)
VALUES (1, 'John', 'Doe', 'Software Engineer', 75000.00);

In this example:

• The INSERT INTO statement is used to specify the name of the table you want to insert data into, which is "employees" in this case.
• The VALUES keyword is used to provide the values you want to insert into the table. Each value corresponds to a column in the table, and the order of the values should match the order of the columns specified in the INSERT INTO statement.

So, in this case, we’re inserting a new employee with the following data:

• employee_id: 1
• first_name: 'John'
• last_name: 'Doe'
• job_title: 'Software Engineer'
• salary: 75000.00

After executing this SQL query, a new row will be added to the “employees” table with this employee’s information.

SQL UPDATE Changes existing data.

An SQL UPDATE query is used to modify existing records in a database table. Here’s a basic example of an SQL UPDATE query:

Suppose you have a table named “employees” with the following data:

+----+-----------+----------+-------------------+----------+
| ID | FirstName | LastName | JobTitle          | Salary   |
+----+-----------+----------+-------------------+----------+
| 1  | John      | Doe      | Software Engineer| 75000.00 |
| 2  | Jane      | Smith    | Database Analyst | 65000.00 |
| 3  | Bob       | Johnson  | Network Engineer | 80000.00 |
+----+-----------+----------+-------------------+----------+

You can use an SQL UPDATE query to change the job title and salary of an employee. For example, let’s update the job title and salary for employee ID 2 (Jane Smith):

UPDATE employees
SET JobTitle = 'Senior Database Analyst', Salary = 70000.00
WHERE ID = 2;

In this example:

• The UPDATE statement is used to specify the name of the table you want to update, which is "employees" in this case.
• The SET keyword is used to specify the columns you want to update and their new values. In this case, we're updating the "JobTitle" to 'Senior Database Analyst' and the "Salary" to 70000.00 for the employee with ID 2.
• The WHERE clause is used to specify which rows to update. Without the WHERE clause, all rows in the table would be updated. In this case, we're using the WHERE clause to specify that we only want to update the employee with ID 2.

After executing this SQL query, the “employees” table will be updated, and the data for employee ID 2 will be changed as follows:

+----+-----------+----------+------------------------+----------+
| ID | FirstName | LastName | JobTitle               | Salary   |
+----+-----------+----------+------------------------+----------+
| 1  | John      | Doe      | Software Engineer     | 75000.00 |
| 2  | Jane      | Smith    | Senior Database Analyst| 70000.00 |
| 3  | Bob       | Johnson  | Network Engineer      | 80000.00 |
+----+-----------+----------+------------------------+----------+

SQL — DELETE Removes data.

An SQL DELETE query is used to remove one or more rows from a database table. Here’s a basic example of an SQL DELETE query:

Suppose you have a table named “employees” with the following data:

+----+-----------+----------+-------------------+----------+
| ID | FirstName | LastName | JobTitle          | Salary   |
+----+-----------+----------+-------------------+----------+
| 1  | John      | Doe      | Software Engineer| 75000.00 |
| 2  | Jane      | Smith    | Database Analyst | 65000.00 |
| 3  | Bob       | Johnson  | Network Engineer | 80000.00 |
+----+-----------+----------+-------------------+----------+

You can use an SQL DELETE query to remove a specific employee from the “employees” table. For example, let’s delete the employee with ID 2 (Jane Smith):

DELETE FROM employees
WHERE ID = 2;

In this example:

• The DELETE FROM statement is used to specify the name of the table from which you want to delete data, which is "employees" in this case.
• The WHERE clause is used to specify the condition that determines which rows should be deleted. In this case, we're using the WHERE clause to specify that we want to delete the row where the "ID" is equal to 2.

After executing this SQL query, the “employees” table will be updated, and the employee with ID 2 (Jane Smith) will be removed from the table. The updated table will look like this:

+----+-----------+----------+-------------------+----------+
| ID | FirstName | LastName | JobTitle          | Salary   |
+----+-----------+----------+-------------------+----------+
| 1  | John      | Doe      | Software Engineer| 75000.00 |
| 3  | Bob       | Johnson  | Network Engineer | 80000.00 |
+----+-----------+----------+-------------------+----------+

Please be cautious when using DELETE queries, as they permanently remove data from the table. Make sure you specify the correct conditions to avoid unintentional data loss.

SQL MERGE: Combines these actions.

The SQL MERGE statement, also known as UPSERT (a combination of UPDATE and INSERT), is used to perform an INSERT, UPDATE, or DELETE operation on a target table based on the condition specified in the source table or subquery. The MERGE statement is typically used when you want to synchronize data between two tables or when you want to perform conditional updates and inserts.

The basic syntax of the SQL MERGE statement is as follows:

MERGE INTO target_table AS target
USING source_table AS source
ON condition
WHEN MATCHED THEN
    UPDATE SET column1 = value1, column2 = value2, ...
WHEN NOT MATCHED THEN
    INSERT (column1, column2, ...) VALUES (value1, value2, ...);

Here’s a breakdown of the components of the SQL MERGE statement:

• target_table: The table you want to modify, which is the target of the MERGE operation.
• source_table: The table or subquery that provides the data you want to use for the INSERT or UPDATE operation.
• condition: The condition that specifies how to match rows between the target and source tables.
• WHEN MATCHED THEN: Specifies what to do when a match is found between the target and source based on the condition. You can perform UPDATE operations here.
• WHEN NOT MATCHED THEN: Specifies what to do when no match is found based on the condition. You can perform INSERT operations here.

Here’s an example of how to use the SQL MERGE statement:

Suppose you have a target table named “employees” and a source table named “new_employees,” and you want to update existing employee data if a match is found based on the employee ID, and insert new employees if there is no match.

MERGE INTO employees AS target
USING new_employees AS source
ON (target.employee_id = source.employee_id)
WHEN MATCHED THEN
    UPDATE SET target.first_name = source.first_name, target.last_name = source.last_name, target.salary = source.salary
WHEN NOT MATCHED THEN
    INSERT (employee_id, first_name, last_name, salary)
    VALUES (source.employee_id, source.first_name, source.last_name, source.salary);

In this example:

• If a match is found between the “employees” and “new_employees” tables based on the “employee_id,” the existing employee’s data (first name, last name, and salary) will be updated.
• If no match is found, a new employee record will be inserted into the “employees” table using data from the “new_employees” table.

The SQL MERGE statement can be a powerful tool for handling data synchronization and conditional updates in SQL databases.