SQL INSERT UPDATE DELETE: Complete Guide with Examples

SQL INSERT UPDATE DELETE commands are fundamental Data Manipulation Language (DML) operations used to manage records in a database. These commands allow you to insert new data, update existing records, and delete unwanted information efficiently. Understanding SQL INSERT UPDATE DELETE statements is essential for database administrators, developers, and data analysts working with relational databases.

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.

SQL INSERT vs UPDATE vs DELETE vs MERGE

Best Practices for SQL INSERT UPDATE DELETE Commands

When working with SQL INSERT UPDATE DELETE commands, you should follow several best practices to maintain data accuracy and improve database performance.

First, always validate data before inserting new records into a table. This approach helps prevent errors and ensures data consistency.

Additionally, use the WHERE clause carefully when updating records. Without a proper condition, you may accidentally modify multiple rows.

Similarly, you should use the WHERE clause when deleting data. Otherwise, the DELETE statement may remove all records from the table.

Furthermore, create backups before performing large UPDATE or DELETE operations. This precaution helps protect important business data.

Moreover, use transactions for critical database operations. Transactions allow you to roll back changes if an error occurs.

In addition, test your queries in a development environment before running them in production. As a result, you can identify potential issues early.

However, avoid executing complex queries without proper indexing. Instead, optimize tables and indexes to improve performance.

Finally, review query results carefully after each operation. Therefore, you can ensure that the database contains accurate and reliable information.

Conclusion

SQL INSERT UPDATE DELETE commands are essential for managing data in relational databases. By mastering INSERT, UPDATE, DELETE, and MERGE statements, you can efficiently maintain database records, improve data accuracy, and support business applications. A strong understanding of these SQL data manipulation techniques is a key skill for every database professional.

Knowledge Check

Frequently Asked Questions