Data Modification (DML)
/tldr: The commands that create, update, and destroy data.
THE CORE IDEA: DML (Create, Update, Delete)
Data Manipulation Language (DML) is used to add, change, or remove data rows from database tables. These operations usually require transactional control (`COMMIT` or `ROLLBACK`) to finalize the changes.
1. INSERT (Adding New Rows)
Standard Single Row
Specifies the columns you are populating, followed by the values for those columns.
INSERT INTO employees (first_name, last_name, salary)
VALUES ('Jane', 'Doe', 75000);
Multiple Rows (Bulk Insert)
Separates each row's set of values with a comma. More efficient than multiple single inserts.
INSERT INTO products (name, price)
VALUES
('Laptop', 1200.00),
('Mouse', 25.00),
('Monitor', 350.00);
2. UPDATE (Modifying Existing Data)
The UPDATE Rule: ALWAYS use WHERE
The SET clause defines the changes. The WHERE clause is **critical** to restrict changes to specific rows. If you omit WHERE, you change the entire table!
-- ⚠️ Update salary for ALL employees in the 'Sales' department
UPDATE employees
SET salary = salary * 1.05,
last_review = CURRENT_DATE
WHERE department = 'Sales';
Using Subqueries in UPDATE
You can update a column based on a value retrieved from another table (e.g., setting a manager's ID).
UPDATE projects
SET manager_id = (
SELECT employee_id
FROM employees
WHERE last_name = 'Smith'
)
WHERE project_name = 'Alpha Launch';
3. DELETE (Removing Rows)
Simple Row Removal
Removes entire rows that meet the WHERE condition. The columns are removed, not just set to NULL.
-- ⚠️ Delete ALL inactive customer records
DELETE FROM customers
WHERE last_activity < DATE('now', '-2 years');
The TRUNCATE Warning (DDL)
Do not confuse DELETE (DML, row by row, can be rolled back) with TRUNCATE TABLE (DDL, removes ALL data fast, cannot usually be rolled back easily).
DELETE FROM log_table; -- Slower, safe
-- TRUNCATE TABLE log_table; -- Dangerous, fast, empties table completely
4. MERGE (Upsert Logic)
The Conditional Command
The MERGE statement (available in Oracle, SQL Server, PostgreSQL, etc.) allows you to perform an INSERT or UPDATE simultaneously based on whether a row exists. This is often called an "Upsert" (Update/Insert).
MERGE INTO target_inventory T
USING source_feed S
ON (T.product_id = S.product_id)
WHEN MATCHED THEN
UPDATE SET T.stock = S.new_stock -- If the row EXISTS, UPDATE it
WHEN NOT MATCHED THEN
INSERT (product_id, stock) VALUES (S.product_id, S.new_stock); -- If it DOES NOT EXIST, INSERT it
Be careful with your writes!