Data Manipulation Language: INSERT, UPDATE, DELETE, and SELECT Basics

What Is Data Manipulation Language?

Data Manipulation Language (DML) is the subset of SQL used to read and change the actual rows stored in a database table. The four core DML statements are INSERT, UPDATE, DELETE, and SELECT. Every web and software application you build will rely on these statements constantly — a user registering an account triggers an INSERT, editing a profile triggers an UPDATE, deleting a post triggers a DELETE, and loading any page almost always triggers a SELECT. Mastering DML is therefore a foundational skill for this trade.

DML statements operate on data only, not on the structure of the database. Statements that create or alter tables belong to a different category called Data Definition Language (DDL). Keep that distinction clear.

INSERT — Adding Rows

Use INSERT to add one or more new rows to a table. Always provide an explicit column list so your code is readable and protected against future table changes.

```sql

INSERT INTO books (title, author_id, genre, published_year, available)

VALUES ('The Great Gatsby', 4, 'Fiction', 1925, TRUE);

```

Rules to remember:

• The column list and the VALUES list must match in order and data type.
• String values go in single quotes; numbers do not.
• If a column has a DEFAULT or is auto-generated (like a serial primary key), you may omit it from the column list.
• PostgreSQL will report an error if a NOT NULL column is missing a value, if a value violates a UNIQUE constraint, or if a foreign key reference does not exist. Read the error message carefully — it tells you exactly which constraint was violated.

UPDATE — Changing Existing Rows

Use UPDATE to change values in rows that already exist. The WHERE clause controls which rows are affected.

```sql

UPDATE books

SET available = FALSE, last_checked_out = CURRENT_DATE

WHERE book_id = 112;

```

Critical safety rule: Always write and test your WHERE clause with a SELECT first before running UPDATE or DELETE. If you omit the WHERE clause, PostgreSQL will apply the change to every row in the table — this is almost never what you want, and recovery requires a backup. In a production environment, wrapping destructive statements in a transaction (BEGIN … COMMIT / ROLLBACK) gives you a chance to verify results before making them permanent.

You can update multiple columns in one statement by separating assignments with commas in the SET clause.

DELETE — Removing Rows

Use DELETE to remove rows from a table.

```sql

DELETE FROM patrons

WHERE patron_id = 305;

```

The same WHERE-clause safety rule applies: verify with SELECT first. A DELETE without a WHERE clause removes all rows from the table while leaving the table structure intact. Use the before-and-after row count pattern — run SELECT COUNT(*) before and after — to confirm exactly how many rows were affected.

Note: If the row you are deleting is referenced by a foreign key in another table, PostgreSQL will refuse the delete and report a foreign key violation error. You must delete or update the referencing rows first, or the schema must define CASCADE behavior.

SELECT — Retrieving Rows

SELECT is the most-used DML statement. The basic structure is:

```sql

SELECT column1, column2

FROM table_name

WHERE condition

ORDER BY column1 ASC;

```

Useful WHERE clause operators:

| Operator | Purpose | Example |

|---|---|---|

| =, <>, <, > | Comparison | genre = 'Mystery' |

| AND, OR, NOT | Combine conditions | available = TRUE AND genre = 'Fiction' |

| BETWEEN | Inclusive range | published_year BETWEEN 1900 AND 1999 |

| IN | Match a list | genre IN ('Fiction', 'History') |

| LIKE | Pattern match | title LIKE 'The%' |

| IS NULL / IS NOT NULL | Test for missing data | return_date IS NULL |

ORDER BY sorts results; ASC (ascending) is the default. Add DESC for descending order. Always use ORDER BY when row sequence matters to your application.

Reading PostgreSQL Error Messages

PostgreSQL error messages follow a consistent format: an error code, a brief message, and often a DETAIL or HINT line. Common DML errors include:

• 23505 unique_violation — you tried to insert a duplicate value into a unique or primary key column.
• 23503 foreign_key_violation — the value references a row that does not exist in the parent table.
• 42703 undefined_column — you spelled a column name wrong.
• 22P02 invalid_input_syntax — the value does not match the column's data type.

Train yourself to read the full error message rather than guessing. The message almost always names the table, column, or constraint involved.

Work Habits and Data Integrity

In this lab environment, mistakes are expected and recoverable. Develop these habits now so they become automatic in professional settings:

1. Preview with SELECT before running UPDATE or DELETE.

2. Check row counts before and after with SELECT COUNT(*).

3. Use transactions for multi-step changes during practice.

4. Never hard-code passwords or sensitive data into SQL scripts you share or commit to version control.

These habits protect data integrity, which is a professional and ethical responsibility for anyone who works with databases holding real personal information.