SQL Data Manipulation Language (DML)

Data Manipulation Language (DML) statements are used to add, change, and remove data in your database. These are the most frequently used commands in SQL.

Core DML Commands

1. INSERT

The INSERT statement is used to add new records (rows) into a table.

Syntax:

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

You can also insert multiple rows at once:

INSERT INTO table_name (column1, column2)
VALUES
    (value1a, value2a),
    (value1b, value2b);
        

INSERT INTO Customers (CustomerID, FirstName, LastName)
VALUES (101, 'Alice', 'Smith');
            

2. UPDATE

The UPDATE statement is used to modify existing records in a table.

Syntax:

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

The WHERE clause is crucial. If omitted, all rows in the table will be updated.

UPDATE Customers
SET LastName = 'Johnson'
WHERE CustomerID = 101;
            

3. DELETE

The DELETE statement is used to remove existing records from a table.

Syntax:

DELETE FROM table_name
WHERE condition;
        

Similar to UPDATE, omitting the WHERE clause will delete all records in the table.

DELETE FROM Customers
WHERE CustomerID = 101;
            

4. SELECT (Data Retrieval)

While often categorized under Data Query Language (DQL), SELECT is fundamental to data manipulation as you often need to retrieve data before deciding how to modify or delete it.

Basic Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition;
        

SELECT FirstName, LastName
FROM Customers
WHERE City = 'New York';
            

Working with Transactions

DML operations are often performed within transactions to ensure data integrity. Transactions group a sequence of operations into a single logical unit of work.

• BEGIN TRANSACTION / START TRANSACTION: Initiates a new transaction.
• COMMIT: Saves all changes made during the transaction permanently.
• ROLLBACK: Undoes all changes made during the transaction.

BEGIN TRANSACTION;

UPDATE Accounts
SET Balance = Balance - 100
WHERE AccountID = 'A';

UPDATE Accounts
SET Balance = Balance + 100
WHERE AccountID = 'B';

-- Check if both updates were successful and balances are valid
-- If all checks pass:
COMMIT;
-- If any check fails or an error occurs:
-- ROLLBACK;
            

Common DML Scenarios

Copying Data Between Tables

You can use INSERT INTO ... SELECT to copy data from one table to another, potentially with modifications or filtering.

INSERT INTO NewTable (ColumnA, ColumnB)
SELECT SourceColumn1, SourceColumn2
FROM SourceTable
WHERE SomeCondition;
        

Merging Data

Often, you'll need to update existing records if they match a condition or insert them if they don't. This is commonly handled with `MERGE` statements (in SQL Server, Oracle) or a combination of `UPDATE` and `INSERT` in other RDBMS.

Bulk Operations

For large amounts of data, consider using database-specific bulk loading utilities or optimized `INSERT` statements. Ensure your transactions are managed efficiently to avoid locking issues and excessive logging.

INSERT INTO Products (Name, Price) VALUES ('Gadget', 19.99);

UPDATE Products SET Price = 21.99 WHERE Name = 'Gadget';

DELETE FROM Products WHERE Name = 'Gadget';

SELECT * FROM Products WHERE Price > 50;

Mastering DML statements is essential for effective database management. Always use the WHERE clause carefully and consider the implications of your operations, especially in production environments.