Essential SQL Data Manipulation: INSERT, UPDATE, and DELETE

Managing data in a database involves various operations, including inserting new records, updating existing records, and deleting records. Additionally, the MERGE statement allows for a more efficient way to perform upserts (inserts or updates). In this section, we will explore these operations using the Employees table.

1. Inserting Data

The INSERT INTO statement is used to add new records to a table. There are several ways to perform inserts:

A. Inserting Single Rows

To insert a single record into the Employees table, you can specify the values directly.

Example:

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary,HireDate, Email)
VALUES (11, 'Laura', 'Green', 'Marketing', 50000, '2023-02-15', 'laura.green@example.com');

B. Inserting Multiple Rows

You can also insert multiple records in a single statement by separating the values with commas.

Example:

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary, HireDate, Email)
VALUES 
(12, 'Nancy', 'Brown', 'Finance', 68000, '2023-03-10', 'nancy.brown@example.com'),
(13, 'Paul', 'Walker', 'IT', 62000, '2023-01-25', 'paul.walker@example.com');

C. Inserting Data from Another Table

You can insert data from one table into another by using a SELECT statement.

Example: Assuming we have another table called TempEmployees that holds new employee data:

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary, HireDate, Email)
SELECT EmployeeID, FirstName, LastName, Department, Salary, HireDate, Email
FROM TempEmployees;

D. Inserting Data with NULL Values

If you want to insert a new employee without specifying an email and letting it be NULL, you could write:

Example:

INSERT INTO Employees (FirstName, LastName, HireDate)
VALUES ('John', 'Doe', '2023-10-10');

In this case, the Email column will automatically be set to NULL since it’s not specified.

If you want to explicitly set the email to NULL, you can do so as follows:

INSERT INTO Employees (FirstName, LastName, HireDate, Email)
VALUES ('John', 'Doe', '2023-10-10', NULL);

Alternatively, you can use the DEFAULT keyword for other fields that may have a default value:

INSERT INTO Employees (FirstName, LastName, HireDate, Email)
VALUES ('John', 'Doe', DEFAULT, DEFAULT);

Note: If you want to insert data and rely on defaults, ensure that the table is designed with default values for the respective columns.

2. Updating Data

The UPDATE statement is used to modify existing records in a table. To update specific records, you must use the WHERE clause to specify which records to change.

Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example:

To update the salary of an employee named John Doe:

UPDATE Employees
SET Salary = 55000
WHERE FirstName = 'John' AND LastName = 'Doe';

After Update: The salary of John Doe will be updated to 55000.

To update multiple columns at once, you can do the following:

UPDATE Employees
SET Department = 'HR', Salary = 60000
WHERE EmployeeID = 8;

3. Deleting Data

The DELETE statement is used to remove existing records from a table. As with the UPDATE statement, it is essential to use the WHERE clause to specify which records to delete.

Syntax:

DELETE FROM table_name
WHERE condition;

Example:

To delete the employee record for Michael Brown:

DELETE FROM Employees
WHERE FirstName = 'Michael' AND LastName = 'Brown';

Note: Be careful with the DELETE statement. If you omit the WHERE clause, all records in the table will be deleted!

To delete all employees in the Marketing department:

DELETE FROM Employees
WHERE Department = 'Marketing';

4. MERGE Statement (Upsert)

The MERGE statement is used to perform upserts, which means it can insert a new record or update an existing one depending on whether a specified condition is met.

Syntax:

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

Example:

Assuming we want to upsert data from a temporary table NewEmployees:

MERGE INTO Employees AS target
USING NewEmployees AS source
ON target.EmployeeID = source.EmployeeID
WHEN MATCHED THEN
    UPDATE SET target.Salary = source.Salary, target.Department = source.Department
WHEN NOT MATCHED THEN
    INSERT (EmployeeID, FirstName, LastName, Department, Salary, HireDate, Email)
    VALUES (source.EmployeeID, source.FirstName, source.LastName, source.Department, source.Salary, source.HireDate, source.Email);

This statement will update existing employees if they match the EmployeeID and insert new records if there is no match.

Conclusion

Inserting, updating, and deleting data are fundamental operations in SQL that allow you to manage your database effectively. The INSERT INTO statement lets you add new records, while the UPDATE and DELETE statements enable you to modify and remove existing records. The MERGE statement provides an efficient way to perform upserts, making it a valuable tool for maintaining data integrity in your applications.

Knowledge Check