Data Modification
## Learning Objectives
- Insert data with INSERT
- Update data with UPDATE
- Delete data with DELETE
- Use transactions safely
## INSERT Statement
### Insert Single Row
```sql
INSERT INTO employees (first_name, last_name, email, salary)
VALUES ('Alice', 'Johnson', 'alice@company.com', 75000);
```
### Insert Multiple Rows
```sql
INSERT INTO employees (first_name, last_name, email, salary)
VALUES
('Bob', 'Smith', 'bob@company.com', 65000),
('Carol', 'Williams', 'carol@company.com', 70000),
('Dave', 'Brown', 'dave@company.com', 60000);
```
### Insert Without Column List
```sql
-- Must provide values for ALL columns in order
INSERT INTO employees
VALUES (4, 'Dave', 'Brown', 'dave@company.com', 60000);
```
### INSERT INTO ... SELECT
```sql
INSERT INTO employees_archive (first_name, last_name, salary)
SELECT first_name, last_name, salary
FROM employees
WHERE hire_date < '2020-01-01';
```
## UPDATE Statement
### Basic Update
```sql
UPDATE employees
SET salary = 80000
WHERE employee_id = 1;
```
### Update Multiple Columns
```sql
UPDATE employees
SET
salary = 80000,
department = 'Engineering'
WHERE employee_id = 1;
```
### Update with Expression
```sql
UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Sales';
```
### UPDATE with Subquery
```sql
UPDATE employees
SET salary = (
SELECT AVG(salary) + 10000
FROM employees
WHERE department = 'Sales'
)
WHERE department = 'Sales'
AND employee_id = 1;
```
## DELETE Statement
### Delete Single Row
```sql
DELETE FROM employees
WHERE employee_id = 5;
```
### Delete Multiple Rows
```sql
DELETE FROM employees
WHERE department = 'Temp';
```
### Delete All Rows
```sql
-- Delete all (use with caution!)
DELETE FROM employees;
-- Faster for large tables (truncates)
TRUNCATE TABLE employees;
```
### DELETE with Subquery
```sql
DELETE FROM employees
WHERE department IN (
SELECT id
FROM departments
WHERE is_active = 0
);
```
## Transactions
### Why Transactions?
Ensure data integrity:
```sql
BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
-- If error occurs:
ROLLBACK;
-- If successful:
COMMIT;
```
### COMMIT and ROLLBACK
```sql
BEGIN; -- or START TRANSACTION;
-- Make changes
INSERT INTO ...
UPDATE ...
-- Save changes
COMMIT;
-- Or undo all changes since BEGIN
ROLLBACK;
```
### Auto-commit Mode
```sql
-- Each statement auto-commits (default in some DBs)
-- Turn off for transaction control:
SET autocommit = 0;
-- Or wrap in explicit transaction:
BEGIN;
-- statements
COMMIT;
```
## INSERT with ON CONFLICT
### PostgreSQL: UPSERT
```sql
INSERT INTO users (id, email, name)
VALUES (1, 'alice@company.com', 'Alice')
ON CONFLICT (id) DO UPDATE
SET email = EXCLUDED.email,
name = EXCLUDED.name;
```
### MySQL: INSERT ... ON DUPLICATE KEY
```sql
INSERT INTO users (id, email, name)
VALUES (1, 'alice@company.com', 'Alice')
ON DUPLICATE KEY UPDATE
email = VALUES(email),
name = VALUES(name);
```
### SQLite: INSERT OR REPLACE
```sql
INSERT OR REPLACE INTO users (id, email, name)
VALUES (1, 'alice@company.com', 'Alice');
```
## UPDATE with JOIN
### MySQL / PostgreSQL
```sql
UPDATE employees e
SET department_name = d.name
FROM departments d
WHERE e.department_id = d.id;
```
### SQL Server
```sql
UPDATE e
SET e.department_name = d.name
FROM employees e
JOIN departments d ON e.department_id = d.id;
```
## DELETE with JOIN
### MySQL
```sql
DELETE e
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.is_active = 0;
```
### PostgreSQL / SQL Server
```sql
DELETE FROM e
USING departments d
WHERE e.department_id = d.id
AND d.is_active = 0;
```
## Returning Modified Rows
### PostgreSQL
```sql
UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Sales'
RETURNING first_name, last_name, salary;
```
### SQLite
```sql
UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Sales';
-- Then query to see changes
```
## Best Practices
1. **Always use WHERE** with UPDATE and DELETE
2. **Test with SELECT first** - write SELECT with same WHERE, then change to UPDATE/DELETE
3. **Use transactions** for multiple related changes
4. **Backup first** for critical operations
### Safety Pattern
```sql
-- 1. See what you're about to change
SELECT *
FROM employees
WHERE department = 'Temp'
AND hire_date < '2023-01-01';
-- 2. If looks right, then delete
DELETE FROM employees
WHERE department = 'Temp'
AND hire_date < '2023-01-01';
```
## Summary
- **INSERT**: Add new rows with VALUES or SELECT
- **UPDATE**: Modify existing rows, use SET
- **DELETE**: Remove rows, use WHERE to avoid accidents
- **BEGIN/COMMIT/ROLLBACK**: Transaction control
- **ON CONFLICT**: Handle duplicate keys (upsert)
- Always backup before bulk operations
- Test with SELECT before modifying
Comments
Comments powered by Giscus
To enable comments, add your Giscus embed code here.
Learn more about Giscus →