Modificación de Datos
## Objetivos de Aprendizaje
- Insertar datos con INSERT
- Actualizar datos con UPDATE
- Eliminar datos con DELETE
- Usar transacciones de forma segura
## Sentencia INSERT
### Insertar Una Fila
```sql
INSERT INTO employees (first_name, last_name, email, salary)
VALUES ('Alice', 'Johnson', 'alice@company.com', 75000);
```
### Insertar Múltiples Filas
```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);
```
### Insertar Sin Lista de Columnas
```sql
-- Debe proporcionar valores para TODAS las columnas en orden
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';
```
## Sentencia UPDATE
### Actualización Básica
```sql
UPDATE employees
SET salary = 80000
WHERE employee_id = 1;
```
### Actualizar Múltiples Columnas
```sql
UPDATE employees
SET
salary = 80000,
department = 'Engineering'
WHERE employee_id = 1;
```
### Actualizar con Expresión
```sql
UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Sales';
```
### UPDATE con Subconsulta
```sql
UPDATE employees
SET salary = (
SELECT AVG(salary) + 10000
FROM employees
WHERE department = 'Sales'
)
WHERE department = 'Sales'
AND employee_id = 1;
```
## Sentencia DELETE
### Eliminar Una Fila
```sql
DELETE FROM employees
WHERE employee_id = 5;
```
### Eliminar Múltiples Filas
```sql
DELETE FROM employees
WHERE department = 'Temp';
```
### Eliminar Todas las Filas
```sql
-- Eliminar todo (usar con precaución!)
DELETE FROM employees;
-- Más rápido para tablas grandes (trunca)
TRUNCATE TABLE employees;
```
### DELETE con Subconsulta
```sql
DELETE FROM employees
WHERE department IN (
SELECT id
FROM departments
WHERE is_active = 0
);
```
## Transacciones
### ¿Por Qué Transacciones?
Asegurar integridad de datos:
```sql
BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
-- Si ocurre un error:
ROLLBACK;
-- Si tiene éxito:
COMMIT;
```
### COMMIT y ROLLBACK
```sql
BEGIN; -- o START TRANSACTION;
-- Hacer cambios
INSERT INTO ...
UPDATE ...
-- Guardar cambios
COMMIT;
-- O deshacer todos los cambios desde BEGIN
ROLLBACK;
```
### Modo Auto-commit
```sql
-- Cada sentencia hace commit automáticamente (por defecto en algunas DBs)
-- Desactivar para control de transacciones:
SET autocommit = 0;
-- O envolver en transacción explícita:
BEGIN;
-- sentencias
COMMIT;
```
## INSERT con 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 con 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 con 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;
```
## Devolver Filas Modificadas
### 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';
-- Luego consultar para ver los cambios
```
## Mejores Prácticas
1. **Siempre usar WHERE** con UPDATE y DELETE
2. **Probar con SELECT primero** - escribir SELECT con el mismo WHERE, luego cambiar a UPDATE/DELETE
3. **Usar transacciones** para múltiples cambios relacionados
4. **Hacer backup primero** para operaciones críticas
### Patrón de Seguridad
```sql
-- 1. Ver qué estás por cambiar
SELECT *
FROM employees
WHERE department = 'Temp'
AND hire_date < '2023-01-01';
-- 2. Si se ve bien, entonces eliminar
DELETE FROM employees
WHERE department = 'Temp'
AND hire_date < '2023-01-01';
```
## Resumen
- **INSERT**: Agregar nuevas filas con VALUES o SELECT
- **UPDATE**: Modificar filas existentes, usar SET
- **DELETE**: Eliminar filas, usar WHERE para evitar accidentes
- **BEGIN/COMMIT/ROLLBACK**: Control de transacciones
- **ON CONFLICT**: Manejar claves duplicadas (upsert)
- Siempre hacer backup antes de operaciones masivas
- Probar con SELECT antes de modificar
Comments
Comments powered by Giscus
To enable comments, add your Giscus embed code here.
Learn more about Giscus →