INSERT, UPDATE y DELETE

🧩 INSERTAR DATOS

Revisar los tipos de datos

💡 Antes de insertar, revisa los tipos de datos de cada columna (ícono de herramienta 🔧 en MySQL Workbench).

🧍 Insertar una sola fila

INSERT INTO customers
VALUES (
   DEFAULT,
   'Jhon', 
   'Smith',
   '1990-01-01',
   NULL,
   'CARRERA',
   'Bogota',
   'BO',
   200);

📤 Resultado (fragmento):

customer_id	first_name	last_name	birth_date	city
9	Jhon	Smith	1990-01-01	Bogota

Ejercicio Insert 1

🧭 Definir el orden de columnas

INSERT INTO customers (
   first_name,
   last_name,
   birth_date,
   address,
   city,
   state,
   points)
VALUES (
   'Jhon',
   'Smith',
   '1990-01-01',
   'CARRERA',
   'Bogota',
   'BO',
   200);

💡 Puedes incluir solo las columnas necesarias. Las omitidas toman el valor DEFAULT o NULL.

INSERT INTO customers (
   last_name,
   first_name,
   birth_date,
   address,
   city, 
   state,
   points)
VALUES (
   'Smith',
   'Jhon',
   '1990-01-01',
   'CARRERA',
   'Bogota',
   'BO',
   200);

📤 Resultado:

customer_id	first_name	last_name	city	points
10	Jhon	Smith	Bogota	200

🧮 INSERTAR MÚLTIPLES FILAS

INSERT INTO shippers (name)
VALUES ('Shipper1'),
       ('Shipper2'),
       ('Shipper3');

📤 Resultado:

shipper_id	name
1	Shipper1
2	Shipper2
3	Shipper3

Ejercicio Insert 2

🧩 Insertar múltiples productos

INSERT INTO products (name, quantity_in_stock, unit_price)
VALUES ('Producto1', 10, 1.95),
       ('Producto2', 11, 1.95),
       ('Producto3', 13, 1.95);

📤 Resultado:

product_id	name	quantity_in_stock	unit_price
11	Producto1	10	1.95
12	Producto2	11	1.95
13	Producto3	13	1.95

🧱 Insertar jerarquías (claves primarias y foráneas)

INSERT INTO orders (customer_id, order_date, status)
VALUES (1, '2019-01-02', 1);

SELECT LAST_INSERT_ID();

💡 Devuelve el último order_id insertado.

INSERT INTO order_items
VALUES (LAST_INSERT_ID(), 1, 1, 2.93),
       (LAST_INSERT_ID(), 2, 2, 3.95);

📤 Resultado:

order_id	product_id	quantity	unit_price
10	1	1	2.93
10	2	2	3.95

📋 Copiar tablas existentes

CREATE TABLE orders_archived AS 
SELECT * FROM orders;

📤 Se crea una copia exacta (sin claves primarias).

Ejercicio Insert 3

🧾 Copiar con JOIN y condiciones

Usando sql_invoicing:

CREATE TABLE archivo_facturas AS
SELECT 
 i.invoice_id,
 i.number,
 c.name AS client,
 i.invoice_total,
 i.payment_total,
 invoice_date,
 payment_date,
 due_date
FROM invoices i
JOIN clients c
  USING (client_id)
WHERE payment_date IS NOT NULL;

📤 Resultado:

invoice_id	client	invoice_total	payment_date
1	Myworks	500	2019-03-01
2	Flextech	250	2019-02-10
4	Omnitech	320	2019-04-05

📥 Subconsultas con INSERT

INSERT INTO orders_archived 
SELECT * 
FROM orders
WHERE order_date < '2019-01-01';

📤 Copia solo las órdenes antiguas (antes de 2019).

🧩 ACTUALIZAR DATOS

Actualizar una fila

UPDATE invoices
SET payment_total = 10, payment_date = '2019-03-01'
WHERE invoice_id = 1;

📤 Resultado:

invoice_id	invoice_total	payment_total	payment_date
1	500	10	2019-03-01

Actualizar varias filas

UPDATE invoices
SET payment_total = invoice_total * 0.5,
    payment_date = due_date
WHERE client_id IN (3,4);

📤 Resultado:

client_id	invoice_total	payment_total	payment_date
3	600	300	2019-05-10
4	800	400	2019-06-12

Ejercicio Insert 4

🔒 Safe Mode

⚠️ Si no te deja actualizar varias filas:

1. Ve a MySQL Workbench → Preferences → SQL Editor
2. Desmarca Safe Updates (Safe Mode)
3. Reconéctate al servidor.

🧮 Subconsultas en UPDATE

SELECT client_id
FROM clients
WHERE name = 'Myworks';

📤 Devuelve, por ejemplo, client_id = 3.

UPDATE invoices
SET payment_total = invoice_total * 0.05,
    payment_date = due_date
WHERE client_id =
      (SELECT client_id
       FROM clients
       WHERE name = 'Myworks');

📤 Actualiza solo las facturas de ese cliente.

Subconsulta con varios valores

UPDATE invoices
SET payment_total = invoice_total * 0.5,
    payment_date = due_date
WHERE client_id IN
  (SELECT client_id
   FROM clients
   WHERE state IN ('CA', 'NY'));

📤 Resultado (fragmento):

client_id	state	payment_total
3	CA	250
7	NY	180
8	CA	400

Ejercicio Insert 5

🪙 Actualización condicional con JOIN

USE sql_store;

UPDATE customers
SET points = points + 1000
WHERE birth_date >= '1990-01-01';

📤 Resultado:

customer_id	first_name	birth_date	points
1	Ana	1995-02-12	2200
3	Luis	1998-04-21	3100
5	María	1990-06-17	4050

✨ Subconsulta aplicada (Cliente Oro)

USE sql_store;

UPDATE orders
SET comments = 'Cliente Oro'
WHERE customer_id IN
  (SELECT customer_id
   FROM customers
   WHERE points > 3000);

📤 Resultado:

order_id	customer_id	comments
6	5	Cliente Oro
8	3	Cliente Oro
9	7	Cliente Oro

Ejercicio Insert 6

🗑️ BORRAR DATOS (DELETE)

USE sql_invoicing;

DELETE FROM invoices
WHERE invoice_id = (
  SELECT invoice_id
  FROM clients
  WHERE name = 'Myworks');

⚠️ Cuidado: Si omites el WHERE, borras toda la tabla. Siempre revisa con un SELECT antes.

📤 Resultado esperado:

invoice_id	client	estado
1	Myworks	✅ Eliminada
2	Flextech	–
3	Omnitech	–

🧠 Resumen final

Acción	Comando	Descripción
Insertar	INSERT INTO ... VALUES	Agrega registros
Copiar tabla	CREATE TABLE ... AS SELECT	Duplica estructura y datos
Actualizar	UPDATE ... SET ... WHERE	Modifica registros
Borrar	DELETE FROM ... WHERE	Elimina filas
Subconsulta	(SELECT ... FROM ...)	Usa resultados dentro de otra sentencia