top of page

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:






Related Posts

See All

SQL PRIMARY KEY Constraint

In SQL, the PRIMARY KEY constraint is used to uniquely identify rows. It is a combination of NOT NULL and UNIQUE constraints i.e. it cannot contain duplicate or NULL values. -- create Colleges table w

SQL Server JOINS - INNER JOIN

INNER JOIN This JOIN returns all records from multiple tables that satisfy the specified join condition. It is the simple and most popular form of join and assumes as a default join. If we omit the IN

Comments


bottom of page