Modifying Data

Data Manipulation Language (DML) allows you to change the data inside your tables. While SELECT is for reading, INSERT, UPDATE, and DELETE are for writing.

Definition: The DML Core

1. INSERT: Adds new rows to a table.
2. UPDATE: Modifies existing values in a table.
3. DELETE: Removes specific rows from a table.
4. TRUNCATE: Removes all rows from a table (extremely fast).

Context: The Dangerous WHERE

When running UPDATE or DELETE, the WHERE clause is your only protection. If you omit it, the command applies to every single row in the table.

Missing WHERE

-- DANGER: This makes everyone's salary zero!
UPDATE Employees SET Salary = 0;

Detailed Explanation: Syntax

1. INSERT

You should always specify the columns you are inserting into.

Bulk Insert

INSERT INTO Products (Name, Price)
VALUES ('New Phone', 999.99), ('Old Phone', 499.99);

2. UPDATE

Batch Update

UPDATE Products
SET Price = Price * 0.9
WHERE Category = 'Electronics';

3. DELETE vs. TRUNCATE

• DELETE: A row-by-row operation. It checks constraints, triggers, and logs every single deletion.
• TRUNCATE: A DDL operation. It simply “de-allocates” the data pages on the disk. It is near-instant even on billions of rows, but it cannot be rolled back in some databases and ignores most triggers.

Example: Managing a Shopping Cart

Step 1: Add an item

INSERT INTO Cart (UserID, ProductID, Qty) VALUES (1, 101, 1);

Step 2: Increase quantity

UPDATE Cart SET Qty = Qty + 1 WHERE UserID = 1 AND ProductID = 101;

Step 3: Remove item

DELETE FROM Cart WHERE UserID = 1 AND ProductID = 101;

Under the Hood: MVCC and WAL

What happens physically when you UPDATE a row?

1. Multi-Version Concurrency Control (MVCC)

In databases like PostgreSQL, an UPDATE doesn’t actually overwrite the old data. Instead, it:

• Marks the old row as “dead.”
• Inserts a completely new version of the row with the updated values. The database later cleans up these dead rows using a process called Vacuum.

2. Write-Ahead Logging (WAL)

Before any change is made to the actual table data on disk, the database writes the change to a Log File (WAL). This ensures that if the power goes out mid-update, the database can “replay” the log to recover the data.

3. Identity and Auto-Increment

Most tables use an “Identity” column for the Primary Key. When you INSERT, you don’t provide a value; the database generates the next number (1, 2, 3…) automatically.

Caution

The Truncate Trap:
TRUNCATE usually resets the “Auto-Increment” counter back to 1. DELETE does not.