Mastering Data Manipulation in SQL: INSERT, UPDATE, DELETE, and MERGE

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.

INSERT: Adding New Data

The INSERT statement is your gateway to injecting fresh data into a database table. It's the tool you use when you need to add new records.

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.

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	Jane	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.00WHERE 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

Remember to adapt the table name, column names, and conditions in the WHERE clause to match your specific database schema and update requirements.

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:

Read more....