Data Manipulation in Relational Databases

Learn the fundamental SQL commands for inserting, updating, and deleting data.

Understanding Data Manipulation Language (DML)

Data Manipulation Language (DML) is a subset of SQL used to retrieve, insert, update, and delete data from a database. These operations are fundamental to managing the information stored within your relational database.

1. Inserting Data: INSERT

The INSERT statement is used to add new rows of data into a table. You can insert a single row or multiple rows at once.

Inserting a Single Row

To insert a single row, you specify the table name and the values for each column. The order of values must match the order of columns in the table definition.

INSERT INTO Customers (CustomerID, CompanyName, ContactName, City, Country)
VALUES (1, 'Acme Corporation', 'John Doe', 'New York', 'USA');

Inserting Multiple Rows

You can insert multiple rows by providing a list of value sets.

INSERT INTO Products (ProductID, ProductName, Price)
VALUES
(101, 'Gadget Pro', 49.99),
(102, 'Super Widget', 19.50),
(103, 'Mega Gizmo', 75.00);

Inserting Specific Columns

If you want to insert values only for certain columns, you can explicitly list the columns you are providing data for. Any columns not listed will receive their default value or NULL if no default is specified.

INSERT INTO Orders (OrderID, CustomerID, OrderDate)
VALUES (5001, 2, '2023-10-27');

2. Updating Data: UPDATE

The UPDATE statement modifies existing records in a table. It's crucial to use a WHERE clause to specify which rows should be updated; otherwise, all rows in the table will be affected.

Updating Specific Rows

This example updates the city for a specific customer.

UPDATE Customers
SET City = 'Los Angeles'
WHERE CustomerID = 1;

Updating Multiple Columns

You can update multiple columns in a single UPDATE statement.

UPDATE Products
SET Price = Price * 1.10, -- Increase price by 10%
    ProductName = CONCAT(ProductName, ' (Updated)')
WHERE ProductID = 102;
Tip: Always test your UPDATE statements with a SELECT query using the same WHERE clause first to ensure you're targeting the correct records.

3. Deleting Data: DELETE

The DELETE statement removes rows from a table. Like UPDATE, it's vital to use a WHERE clause to avoid unintended data loss.

Deleting Specific Rows

This command deletes a specific customer record.

DELETE FROM Customers
WHERE CustomerID = 1;

Deleting All Rows

To remove all rows from a table, you can use DELETE FROM TableName;. However, for large tables, TRUNCATE TABLE TableName; is often more efficient as it deallocates the data pages.

-- Deletes all rows. Use with extreme caution!
DELETE FROM Orders;
Caution: Once data is deleted, it's usually unrecoverable without backups. Always ensure you have a reliable backup strategy in place before performing bulk delete operations.

4. Data Integrity and DML

DML operations must adhere to the database's integrity constraints (e.g., foreign keys, unique constraints, NOT NULL). If an operation violates a constraint, the database will typically reject the operation and return an error.

Example: Foreign Key Constraint Violation

If you try to delete a customer who has existing orders, and a foreign key constraint is set up to prevent this, the DELETE statement will fail.

-- Assume CustomerID 2 has orders. This might fail if ON DELETE NO ACTION is set.
DELETE FROM Customers
WHERE CustomerID = 2;

Conclusion

Mastering INSERT, UPDATE, and DELETE is essential for effective database management. Always practice these operations on development or staging environments before applying them to production data.