Data Manipulation (INSERT, UPDATE, DELETE)

40 min

Data manipulation statements (INSERT, UPDATE, DELETE) modify data in database tables. These statements enable you to add new records, modify existing records, and remove records. Understanding data manipulation is essential for maintaining database content. These operations must be used carefully to avoid data loss or corruption. Always test data manipulation statements before running them on production data.

The `INSERT INTO` statement adds new records to a table. You can insert single rows or multiple rows in one statement. INSERT can specify column names or insert into all columns. INSERT with VALUES inserts specific values, while INSERT INTO ... SELECT inserts data from another query. Understanding INSERT enables you to add data to tables. INSERT operations can fail due to constraint violations (primary keys, foreign keys, NOT NULL, etc.).

The `UPDATE` statement modifies existing records in a table. UPDATE can change one or more columns for rows matching a WHERE condition. UPDATE without WHERE affects all rows (dangerous!). Always use WHERE with UPDATE unless you intend to update all rows. Understanding UPDATE enables you to modify data. UPDATE operations should be tested carefully, especially with complex WHERE conditions.

The `DELETE` statement removes existing records from a table. DELETE removes entire rows, not individual column values. DELETE without WHERE removes all rows (very dangerous!). Always use WHERE with DELETE unless you intend to delete all rows. Understanding DELETE enables you to remove data. DELETE operations are permanent—use transactions for safety.

The `TRUNCATE TABLE` statement removes all rows from a table more efficiently than DELETE. TRUNCATE is faster because it doesn't log individual row deletions and resets auto-increment counters. TRUNCATE cannot be rolled back in some databases. TRUNCATE cannot be used with tables referenced by foreign keys in some databases. Understanding TRUNCATE helps you choose the right method for clearing tables.

Best practices include always using WHERE with UPDATE and DELETE (unless intentionally updating/deleting all rows), testing data manipulation statements on sample data first, using transactions for safety, backing up data before major changes, and understanding constraints that might prevent operations. Data manipulation requires careful attention to avoid mistakes.

Key Concepts

INSERT adds new records to tables.
UPDATE modifies existing records in tables.
DELETE removes records from tables.
Always use WHERE with UPDATE and DELETE to avoid unintended changes.
TRUNCATE removes all rows more efficiently than DELETE.

Learning Objectives

Master

Inserting new records with INSERT INTO
Updating existing records with UPDATE
Deleting records with DELETE
Understanding when to use TRUNCATE vs DELETE

Develop

Data management thinking
Understanding data modification safety
Designing safe data manipulation operations

Tips

Always use WHERE with UPDATE and DELETE—test the WHERE clause with SELECT first.
Use transactions for data manipulation operations for safety.
Back up data before major UPDATE or DELETE operations.
Test data manipulation statements on sample data first.

Common Pitfalls

Running UPDATE or DELETE without WHERE, affecting unintended rows.
Not testing WHERE clauses before UPDATE/DELETE, causing data loss.
Not understanding constraints, causing INSERT/UPDATE/DELETE to fail.
Not using transactions, making it hard to roll back mistakes.

Summary

INSERT adds new records, UPDATE modifies existing records, DELETE removes records.
Always use WHERE with UPDATE and DELETE to avoid unintended changes.
TRUNCATE removes all rows more efficiently than DELETE.
Understanding data manipulation enables effective database maintenance.
Data manipulation requires careful attention to avoid mistakes.

Exercise

Write an `INSERT` statement to add a new customer to the `Customers` table.

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

Data Manipulation (INSERT, UPDATE, DELETE)