INSERT — Adding Data
Single Row Insert
INSERT INTO employees (first_name, last_name, email, hire_date, salary)
VALUES ('Alice', 'Johnson', 'alice@company.com', '2024-01-15', 75000.00);
Multiple Rows Insert
INSERT INTO employees (first_name, last_name, email, hire_date, salary)
VALUES
('Bob', 'Smith', 'bob@company.com', '2024-02-01', 82000.00),
('Charlie', 'Brown', 'charlie@company.com', '2024-02-15', 68000.00),
('Diana', 'Lee', 'diana@company.com', '2024-03-01', 91000.00);
Insert from Another Table
INSERT INTO employees_backup (first_name, last_name, email)
SELECT first_name, last_name, email
FROM employees
WHERE hire_date < '2023-01-01';
💡 Always specify column names in INSERT statements. It makes your code clearer and protects against schema changes.
Common INSERT Errors
| Error | Cause | Solution |
|---|---|---|
| Column count mismatch | Values don't match columns | Specify column names |
| Duplicate primary key | ID already exists | Use AUTO_INCREMENT or check ID |
| NOT NULL violation | Missing required value | Provide value or use DEFAULT |
| Data type mismatch | Wrong type for column | Match value type to column type |
UPDATE — Modifying Data
Basic Update
UPDATE employees
SET salary = 85000.00
WHERE employee_id = 1;
Update Multiple Columns
UPDATE employees
SET salary = 85000.00,
department = 'Engineering',
phone = '555-0123'
WHERE employee_id = 1;
Update with Calculations
-- Give all employees a 5% raise
UPDATE employees
SET salary = salary * 1.05
WHERE is_active = TRUE;
-- Apply department-specific raises
UPDATE employees
SET salary = CASE
WHEN department = 'Engineering' THEN salary * 1.10
WHEN department = 'Sales' THEN salary * 1.08
ELSE salary * 1.05
END
WHERE is_active = TRUE;
⚠️ Never run UPDATE without a WHERE clause unless you intentionally want to update ALL rows. This is one of the most common and costly mistakes in SQL.
Update with JOIN
UPDATE orders o
JOIN customers c ON o.customer_id = c.customer_id
SET o.status = 'VIP'
WHERE c.total_purchases > 10000;
DELETE — Removing Data
Delete Specific Rows
DELETE FROM employees
WHERE employee_id = 5;
Delete with Conditions
-- Delete inactive accounts older than 2 years
DELETE FROM accounts
WHERE is_active = FALSE
AND last_login < DATE_SUB(CURRENT_DATE, INTERVAL 2 YEAR);
Delete All Rows
DELETE FROM temp_data;
-- Table remains, all rows removed
Delete with JOIN
DELETE o FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.is_banned = TRUE;
DELETE vs TRUNCATE vs DROP
| Operation | What It Does | Speed | Rollback | Resets AUTO_INCREMENT |
|---|---|---|---|---|
DELETE | Removes specific rows | Slow | Yes | No |
TRUNCATE | Removes all rows | Fast | No | Yes |
DROP | Removes entire table | Fastest | No | N/A |
⚠️ TRUNCATE and DROP cannot be rolled back in most databases. Use DELETE when you need the ability to undo.
Safe Data Modification Practices
-- Always use transactions for bulk changes
BEGIN TRANSACTION;
UPDATE employees SET salary = salary * 1.10
WHERE department = 'Engineering';
-- Verify the change
SELECT COUNT(*) FROM employees
WHERE department = 'Engineering'
AND salary = salary * 1.10;
-- If satisfied, commit; otherwise rollback
COMMIT;
-- or
ROLLBACK;
✏️ Exercise: Write a query to update the price of all products in the 'Electronics' category by increasing them by 15%, then write a query to delete all orders older than 5 years.
See Solution
-- Update prices
UPDATE products
SET price = price * 1.15
WHERE category = 'Electronics';
-- Delete old orders
DELETE FROM orders
WHERE order_date < DATE_SUB(CURRENT_DATE, INTERVAL 5 YEAR);
✅ Key Takeaways
- Always specify column names in INSERT statements for clarity and safety
- Never run UPDATE without WHERE unless you want all rows changed
- DELETE removes rows, TRUNCATE removes all rows, DROP removes the table
- Use transactions for bulk data modifications
- BACKUP data before running large DELETE or UPDATE operations