SQL DML Documentation

Introduction to DML

Data Manipulation Language (DML) is a subset of SQL commands that are used to manage data within schema objects. DML commands are primarily used for inserting, updating, deleting, and retrieving data from your database. These operations are fundamental to interacting with and managing the information stored in relational databases.

Unlike Data Definition Language (DDL) which defines the database structure, or Data Control Language (DCL) which manages permissions, DML focuses solely on the data itself.

DML Statement Overview

The core DML statements include:

INSERT: Adds new rows of data into a table.
UPDATE: Modifies existing data in one or more rows of a table.
DELETE: Removes one or more rows of data from a table.
SELECT: Retrieves data from one or more tables. This is often considered the most frequently used DML statement.
MERGE: Performs conditional INSERT, UPDATE, or DELETE operations on a target table based on join conditions with a source.

These statements are essential for any database application that needs to interact with its data dynamically.

INSERT Statement

The INSERT statement is used to add new records (rows) to a table. You can insert a single row or multiple rows at once.

Syntax


INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

-- Inserting multiple rows
INSERT INTO table_name (column1, column2)
VALUES
    (value1_row1, value2_row1),
    (value1_row2, value2_row2);

-- Inserting data from another table
INSERT INTO table_name (column1, column2)
SELECT column_a, column_b FROM another_table WHERE condition;

Example:

To insert a new employee record into an Employees table:


INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, HireDate)
VALUES (101, 'Jane', 'Doe', 'Marketing', '2023-10-26');

If you omit the column list, you must provide values for all columns in the order they appear in the table definition.

UPDATE Statement

The UPDATE statement modifies existing records in a table. It's crucial to use the WHERE clause to specify which rows to update.

Syntax


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

Example:

To update the department of an employee with EmployeeID 101:


UPDATE Employees
SET Department = 'Sales'
WHERE EmployeeID = 101;

Omitting the WHERE clause will update ALL rows in the table. Use with extreme caution!

DELETE Statement

The DELETE statement removes rows from a table. Similar to UPDATE, the WHERE clause is essential for targeting specific rows.

Syntax


DELETE FROM table_name
WHERE condition;

Example:

To delete all employees from the 'Intern' department:


DELETE FROM Employees
WHERE Department = 'Intern';

Omitting the WHERE clause will delete ALL rows from the table. This action is usually irreversible without backups.

SELECT Statement

The SELECT statement is used to query the database and retrieve data. It is arguably the most powerful and frequently used DML command.

Syntax


SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column1 ASC|DESC;

-- Selecting all columns
SELECT *
FROM table_name;

-- Using aggregate functions
SELECT COUNT(column1), AVG(column2)
FROM table_name
WHERE condition;

Example:

To retrieve the first name and hire date of all employees in the 'Sales' department, ordered by hire date:


SELECT FirstName, HireDate
FROM Employees
WHERE Department = 'Sales'
ORDER BY HireDate ASC;

SELECT statements do not modify data; they only retrieve it.

MERGE Statement

The MERGE statement (also known as UPSERT in some contexts) allows you to perform INSERT, UPDATE, or DELETE operations on a target table based on the results of a join with a source table or query.

Syntax


MERGE INTO target_table AS T
USING source_table AS S
ON T.join_column = S.join_column
WHEN MATCHED THEN
    UPDATE SET T.column1 = S.value1, T.column2 = S.value2
WHEN NOT MATCHED BY TARGET THEN
    INSERT (column1, column2) VALUES (S.value1, S.value2)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE; -- Optional DELETE clause

Example:

Synchronizing employee data from a staging table to the main table:


MERGE INTO Employees AS T
USING StagingEmployees AS S
ON T.EmployeeID = S.EmployeeID
WHEN MATCHED THEN
    UPDATE SET T.FirstName = S.FirstName, T.Department = S.Department
WHEN NOT MATCHED BY TARGET THEN
    INSERT (EmployeeID, FirstName, LastName, Department)
    VALUES (S.EmployeeID, S.FirstName, S.LastName, S.Department);

The exact syntax and support for MERGE can vary slightly between different SQL database systems (e.g., SQL Server, Oracle, PostgreSQL).

Advanced DML Concepts

Beyond the basic statements, DML encompasses several advanced concepts:

Subqueries: Using SELECT statements within other DML statements to filter or provide values.
Joins: Combining data from multiple tables in SELECT, UPDATE, and DELETE statements.
Transactions: Grouping DML operations into atomic units (BEGIN TRANSACTION, COMMIT, ROLLBACK) to ensure data integrity.
Triggers: Stored procedures that automatically execute in response to certain DML events on a table.
Common Table Expressions (CTEs): Temporary, named result sets that can be referenced within a single SQL statement, often simplifying complex DML queries.

Mastering these concepts is key to building robust and efficient database applications.