ON DUPLICATE KEY en MySQL práctico

Contenido del curso

Introducción

CREATE

INSERT

Bash y archivos SQL

14
Cómo importar archivos SQL con MySQL
07:18 min

SELECT

Consultas en MySQL

Contenido Bonus

24
MySQL en terminal: datos a decisiones
15:24 min

Tomar examen

Cursos Empresas Blog Live Conf Eventos Precios

0:009:04

ON DUPLICATE KEY en MySQL práctico

Curso de SQL y MySQL 2018

Clase 12 de 24

Clase 12 / 24

Recursos

Domina el control de duplicados en MySQL con ejemplos reales: aprende a trabajar con columnas únicas como el email, interpreta el Error 1062 y aplica de forma segura la sentencia ON DUPLICATE KEY para actualizar registros sin romper tu modelo de datos.

¿Cómo validar datos de clientes con llaves únicas?

En la base de datos se trabaja con tablas de autores y clientes. La tabla de clientes define una llave primaria (ID) y un email único por decisión de negocio. Esto garantiza que no existan dos tuplas con el mismo correo electrónico.

Llave primaria: identificador único por defecto en cada tabla.
Columna única (email): restricción de negocio para evitar duplicados.
Tupla: fila de datos en la tabla.
Campo active: indica si el cliente está activo (1) o no (0).

Consultas básicas usadas:

SELECT * FROM authors;
SELECT * FROM clients;
DESCRIBE clients;

Los datos cargados incluyen cuatro clientes con IDs 1, 2, 3 y 4, todos con email definido y el campo active en 1.

¿Qué sucede al repetir un email?

Al intentar insertar a Pedro Sánchez de nuevo, aún quitando el ID, el sistema detecta el email duplicado y devuelve el clásico error de MySQL:

Error 1062: duplicate key en la llave email.

Este comportamiento protege la integridad de datos sin que tengas que hacer validaciones manuales adicionales.

¿Cómo preparar el insert para actualizar si hay duplicado?

La solución propuesta es usar ON DUPLICATE KEY para actualizar campos cuando la clave única ya existe. Por ejemplo, cambiar el estado activo del cliente existente con los valores del intento de inserción:

INSERT INTO clients (name, email, active)
VALUES ('Pedro Sánchez', 'correo@ejemplo.com', 0)
ON DUPLICATE KEY UPDATE active = VALUES(active);

Idea clave: si la clave ya existe, se actualiza la tupla con el nuevo valor de active. En el video se comenta que usar email para actualizar no tiene sentido; el foco está en campos como active.

¿Cómo usar ON DUPLICATE KEY sin malas prácticas?

El docente advierte sobre una opción documentada que no recomienda: IGNORE ALL. La instrucción es clara: no la uses. Ignorar errores los invisibiliza y puede llevar a comportamientos peligrosos en producción.

Los errores deben informar y guiar decisiones.
Ocultar errores hace más difícil mantener la calidad de datos.
Diseña flujos que reaccionen a los errores, no que los silencien.

Patrones mostrados para actualizar al detectar duplicado:

-- Patrón recomendado
INSERT INTO clients (name, email, active)
VALUES ('Pedro Sánchez', 'correo@ejemplo.com', 0)
ON DUPLICATE KEY UPDATE active = VALUES(active);

También se comenta una variante intermedia con UPDATE SET, y se aclara que el SET sobraba en ese contexto, dejando la forma directa UPDATE active = VALUES(active) como la elección correcta en el ejemplo.

Beneficios prácticos mencionados:

Actualizar datos de clientes sin operaciones separadas de SELECT + UPDATE.
Integrar información de distintos proveedores priorizando campos: si está vacío, ponlo; si ya existe y es válido, consérvalo.
Acelerar flujos de carga evitando errores por duplicados controlados.

¿Qué trucos de consola y consultas mejoran tu flujo?

Para inspeccionar un registro específico se usó SELECT con filtro por ID del cliente, y un formato de salida más legible en la terminal.

SELECT * FROM clients WHERE client_id = 4;

Historial en terminal: usa la flecha hacia arriba para recuperar el último comando.
Presentación columnar: termina la consulta con \G en lugar de ; para una vista vertical más clara.

Ejemplo con salida vertical:

SELECT * FROM clients WHERE client_id = 4\G

Próximo tema anunciado: un insert con selects anidados. No es el caso más estándar, pero puede ser útil si se usa con cuidado para mezclar datos y obtener IDs correctos al vuelo.

¿Te quedaste con alguna duda sobre ON DUPLICATE KEY, llaves únicas o el manejo del Error 1062? Comparte tu caso en los comentarios y afinamos la consulta juntos.

Royer Guerrero Pinilla

Hace 7 años

Notas de Clase

-- Creacion de base de datos
CREATE DATABASE platzi_operation;
-- Creacion de base de datos usando un condicional
CREATE DATABASE IF NOT EXISTS platzi_operation;
-- Mostar Warnings
SHOW warnings;
-- Mostar bases de datos
SHOW DATABASES
-- Utilizar una base de datos
USE platzi_operation;
-- Mostrar tablas
SHOW TABLES;
-- Como saber que base de datos estoy usando, RTA:
SELECT DATABASE();
-- Borrar tabla
DROP TABLE books
-- Mostar tablas
SHOW TABLES;
-- Describe los campos de la base de datos
DESCRIBE books
DESC authors
-- Mostar todo el esquema de la tabla (Incluyendo los comentarios)
SHOW FULL COLUMNS FROM books;

-- CREACION DEL PROYECTO [LIBRERIA]

-- Buena practica: Usar el nombre de la tabla en plural
-- Tabla de libros
CREATE TABLE IF NOT EXISTS books(
  book_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  author_id INT UNSIGNED ,
  title VARCHAR(100) NOT NULL,
  `year` INT UNSIGNED NOT NULL DEFAULT 1900,
  language VARCHAR(2) NOT NULL DEFAULT 'es' COMMENT 'ISO 639-1 Lenguage',
  cover_url VARCHAR(500),
  price DOUBLE(6,2) DEFAULT 10.0,
  sellable TINYINT(1) DEFAULT 1,
  copies INT NOT NULL DEFAULT 1,
  description TEXT
);

-- Tabla de autores
CREATE TABLE IF NOT EXISTS authors (
  author_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  nationality VARCHAR(3)
);

-- Tabla de clientes
CREATE TABLE IF NOT EXISTS clients(
  client_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL UNIQUE,
  birthdate DATETIME,
  gender ENUM('M', 'F', 'ND') NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
    ON UPDATE CURRENT_TIMESTAMP
);

-- Tabla de operaciones
CREATE TABLE IF NOT EXISTS operations(
  operation_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  book_id INT UNSIGNED,
  client_id INT UNSIGNED,
  `type` ENUM('prestado', 'devuelto', 'vendido'),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP,
  finshed TINYINT(1) NOT NULL
);

-- Inserccion de datos

-- Relleno de autores
INSERT INTO authors(author_id, name, nationality) 
VALUES (NULL, 'Juan Rulfo', 'MEX');

INSERT INTO authors(name, nationality) 
VALUES('Gabriel Garcia Marquez', 'COL');

INSERT INTO authors 
VALUES(NULL, 'Juan Gabriel Vazquez', 'COL'); 

-- Relleno con TRUCASOOOOOOO : CHUNCKS 50 Num Magic
INSERT INTO authors(name, nationality)
VALUES ('Julio Cortázar', 'ARG'),
       ('Isabel Allende', 'CHI'),
       ('Octavio Paz', 'MEX'),
       ('Juan Carlos Onetti', 'URY');

-- Llave Perdida
INSERT INTO authors(author_id, name) 
VALUES(16, 'Pablo Neruda');

-- Autores

INSERT INTO `authors`(name, nationality) 
VALUES ('Sam Altman','USA'),
('Freddy Vega','COL'),
('Arthur Conan Doyle','GBR'),
('Chuck Palahniuk','USA'),
('Juan Rulfo','MEX'),
('Henning Mankel','SWE'),
('Jaideva Goswami','USA'),
('John Foreman','USA'),
('Stephen Hawking','USA'),
('Stephen Dubner','USA'),
('Edward Said','USA'),
('Vladimir Vapnik','RUS'),
('V P Menon','IND'),
('Leonard Mlodinow','USA'),
('Frank Shih','JAP'),
('Maria Konnikova','RUS'),
('Sebastian Gutierrez','ESP'),
('Kurt Vonnegut','USA'),
('Cedric Villani','FRA'),
('Gerald Sussman','USA'),
('Abraham Eraly','IND'),
('Frank Kafka','AUT'),
('John Pratt','USA'),
('Robert Nisbet','USA'),
('H. G. Wells',"ENG"),
('Werner Heisenberg','DEU'),
('Andy Oram',NULL),
('Terence Tao',"AUS"),
('Drew Conway',"USA"),
('Nate Silver',"USA"),
('Wes McKinney',"USA"),
('Thomas Cormen', "USA"),
('Siddhartha Deb',"IND"),
('Albert Camus',"FRA"),
('Adam Smith',"ENG"),
('Ken Follett',"ENG"),
('Fritjof Capra',"AUT"),
('Richard Feynman',"USA"),
('Ernest Hemingway',"USA"),
('Frederick Forsyth',"ENG"),
('Jeffery Archer',"ENG"),
('Randy Pausch',"USA"),
('Ayn Rand',"RUS"),
('Michael Crichton',"USA"),
('John Steinbeck',"USA"),
('Edgar Allen Poe',"USA"),
('Will Durant',NULL),
('P L Deshpande',NULL),
('John Grisham',"USA"),
('V. S. Naipaul',NULL),
('Joseph Heller',NULL),
('BBC',NULL),
('Bob Dylan',"USA"),
('Madan Gupta',"IND"),
('Alfred Stonier',NULL),
('W. H. Greene',NULL),
('Gary Bradsky',NULL),
('Andrew Tanenbaum',NULL),
('David Forsyth',NULL),
('Schilling Taub',NULL),
('Yashwant Kanetkar',NULL),
('Jonathan Stroud',NULL),
('Fyodor Dostoevsky',"RUS"),
('Dan Brown',"USA"),
('Amartya Sen',NULL),
('Amitav Ghosh',NULL),
('Lorraine Hansberry',NULL),
('Bob Woodward',NULL),
('Kuldip Nayar',NULL),
('Sunita Deshpande',NULL),
('William Dalrymple',NULL),
('Various',NULL),
('Sanjay Garg',NULL),
('V P Kale',NULL),
('Shashi Tharoor',"IND"),
('Dominique Lapierre',NULL),
('Bertrand Russell',"ENG"),
('Sam Harris',NULL),
('Earle Stanley Gardner',NULL),
('Peter Drucker',NULL),
('David Bodanis',NULL),
('Victor Hugo',"FRA"),
('Richard Gordon',NULL),
('George Orwell',NULL),
('Lee Iacoca',"USA"),
('William S Maugham',NULL),
('Robert Pirsig',NULL),
('Robert Fisk',NULL),
('Amir Aczel',NULL),
('Samuel Huntington',NULL),
('Richard Bach',NULL),
('Braithwaite',NULL),
('V S Naipaul',NULL),
('Jawaharlal Nehru',NULL),
('Gerald Durrell',NULL),
('Simon Singh',"ENG"),
('Hart Duda',NULL),
('Thomas Friedman',NULL),
('Keith Devlin',NULL),
('James Gleick',NULL),
('Joy Thomas',NULL),
('Muhammad Rashid',NULL),
('Ned Mohan',NULL),
('Simon Haykin',NULL),
('Alex Rutherford',NULL),
('Michael Baz-Zohar',NULL),
('Jim Corbett',NULL),
('Jules Verne',NULL),
('Deshpande P L',NULL),
('Eric Raymond',NULL),
('Sergio Franco',NULL),
('Allen Downey',NULL),
('Morris West',NULL),
('Phillip Janert',NULL),
('Carl Sagan',"USA"),
('E T Bell',NULL),
('Richard Dawkins',NULL),
('Sudhanshu Ranjan',"IND"),
('Kautiyla',NULL),
('Palkhivala',NULL),
('Sorabjee',NULL),
('Hussain Zaidi',NULL),
('Peter Ackroyd',NULL),
('Nariman',NULL),
('Jean Sassoon',NULL),
('Peter Dickinson',NULL),
('Machiavelli',NULL),
('Aldous Huxley',"ENG"),
('J K Rowling',"ENG"),
('Steig Larsson',"SWE"),
('Steve Eddins', NULL),
('Charles Dickens',"ENG");

-- Insercion de libros

INSERT INTO `books` VALUES (1,1,'The Startup Playbook',2013,'en',NULL,10.00,1,5,'Advice from the experts'),
(2,1,'The Startup Playbook',2014,'es',NULL,10.00,1,5,'Consejo de los expertos, traducido por Platzi'),
(3,3,'Estudio en escarlata',1887,'es',NULL,5.00,1,10,'La primera novela de Sherlock Holmes'),
(4,6,'Wallander: Asesinos sin rostro',1991,'es',NULL,15.00,1,3,''),
(5,6,'Wallander: Los perros de Riga',1992,'es',NULL,15.00,1,3,''),
(6,6,'Wallander: La leona blanca',1993,'es',NULL,15.00,1,3,''),
(7,6,'Wallander: El hombre sonriente',1994,'es',NULL,15.00,1,3,''),
(8,6,'Wallander: La falsa pista',1995,'es',NULL,15.00,1,3,''),
(9,6,'Wallander: La quinta mujer',1996,'es',NULL,15.00,1,3,''),
(10,6,'Wallander: Pisando los talones',1997,'es',NULL,15.00,1,3,''),
(11,6,'Wallander: Cortafuegos',1998,'es',NULL,15.00,1,3,''),
(12,5,'El llano en llamas',1953,'es',NULL,10.00,0,1,'Cuentos mexicanos'),
(13,7,'Fundamentals of Wavelets',1900,'en',NULL,NULL,1,4,NULL),
(14,8,'Data Smart',1900,'en',NULL,NULL,1,4,NULL),
(15,9,'God Created the Integers',1900,'en',NULL,NULL,1,4,NULL),
(16,10,'Superfreakonomics',1900,'en',NULL,NULL,1,4,NULL),
(17,11,'Orientalism',1900,'en',NULL,NULL,1,4,NULL),
(18,12,'The Nature of Statistical Learning Theory',1900,'en',NULL,NULL,1,4,NULL),
(19,13,'Integration of the Indian States',1900,'en',NULL,NULL,1,4,NULL),
(20,14,'The Drunkard's Walk',1900,'en',NULL,NULL,1,4,NULL),
(21,15,'Image Processing & Mathematical Morphology',1900,'en',NULL,NULL,1,4,NULL),
(22,16,'How to Think Like Sherlock Holmes',1900,'en',NULL,NULL,1,4,NULL),
(23,17,'Data Scientists at Work',1900,'en',NULL,NULL,1,4,NULL),
(24,18,'Slaughterhouse Five',1900,'en',NULL,NULL,1,4,NULL),
(25,19,'Birth of a Theorem',1900,'en',NULL,NULL,1,4,NULL),
(26,20,'Structure & Interpretation of Computer Programs',1900,'en',NULL,NULL,1,4,NULL),
(27,21,'The Age of Wrath',1900,'en',NULL,NULL,1,4,NULL),
(28,22,'The Trial',1900,'en',NULL,NULL,1,4,NULL),
(29,23,'Statistical Decision Theory',1900,'en',NULL,NULL,1,4,NULL),
(30,24,'Data Mining Handbook',1900,'en',NULL,NULL,1,4,NULL),
(31,25,'The New Machiavelli',1900,'en',NULL,NULL,1,4,NULL),
(32,26,'Physics & Philosophy',1900,'en',NULL,NULL,1,4,NULL),
(33,27,'Making Software',1900,'en',NULL,NULL,1,4,NULL),
(34,28,'Vol I Analysis',1900,'en',NULL,NULL,1,4,NULL),
(35,29,'Machine Learning for Hackers',1900,'en',NULL,NULL,1,4,NULL),
(36,30,'The Signal and the Noise',1900,'en',NULL,NULL,1,4,NULL),
(37,31,'Python for Data Analysis',1900,'en',NULL,NULL,1,4,NULL),
(38,32,'Introduction to Algorithms',1900,'en',NULL,NULL,1,4,NULL),
(39,33,'The Beautiful and the Damned',1900,'en',NULL,NULL,1,4,NULL),
(40,34,'The Outsider',1900,'en',NULL,NULL,1,4,NULL),
(41,3,'The - Vol I Complete Sherlock Holmes',1900,'en',NULL,NULL,1,4,NULL),
(42,3,'The - Vol II Complete Sherlock Holmes',1900,'en',NULL,NULL,1,4,NULL),
(43,37,'The Wealth of Nations',1900,'en',NULL,NULL,1,4,NULL),
(44,38,'The Pillars of the Earth',1900,'en',NULL,NULL,1,4,NULL),
(45,39,'The Tao of Physics',1900,'en',NULL,NULL,1,4,NULL),
(46,40,'Surely You's re Joking Mr Feynman',1900,'en',NULL,NULL,1,4,NULL),
(47,41,'A Farewell to Arms',1900,'en',NULL,NULL,1,4,NULL),
(48,42,'The Veteran',1900,'en',NULL,NULL,1,4,NULL),
(49,43,'False Impressions',1900,'en',NULL,NULL,1,4,NULL),
(50,44,'The Last Lecture',1900,'en',NULL,NULL,1,4,NULL),
(51,45,'Return of the Primitive',1900,'en',NULL,NULL,1,4,NULL),
(52,46,'Jurassic Park',1900,'en',NULL,NULL,1,4,NULL),
(53,47,'A Russian Journal',1900,'en',NULL,NULL,1,4,NULL),
(54,48,'Tales of Mystery and Imagination',1900,'en',NULL,NULL,1,4,NULL),
(55,10,'Freakonomics',1900,'en',NULL,NULL,1,4,NULL),
(56,39,'The Hidden Connections',1900,'en',NULL,NULL,1,4,NULL),
(57,51,'The Story of Philosophy',1900,'en',NULL,NULL,1,4,NULL),
(58,52,'Asami Asami',1900,'en',NULL,NULL,1,4,NULL),
(59,47,'Journal of a Novel',1900,'en',NULL,NULL,1,4,NULL),
(60,47,'Once There Was a War',1900,'en',NULL,NULL,1,4,NULL),
(61,47,'The Moon is Down',1900,'en',NULL,NULL,1,4,NULL),
(62,56,'The Brethren',1900,'en',NULL,NULL,1,4,NULL),
(63,57,'In a Free State',1900,'en',NULL,NULL,1,4,NULL),
(64,58,'Catch 22',1900,'en',NULL,NULL,1,4,NULL),
(65,59,'The Complete Mastermind',1900,'en',NULL,NULL,1,4,NULL),
(66,60,'Dylan on Dylan',1900,'en',NULL,NULL,1,4,NULL),
(67,61,'Soft Computing & Intelligent Systems',1900,'en',NULL,NULL,1,4,NULL),
(68,62,'Textbook of Economic Theory',1900,'en',NULL,NULL,1,4,NULL),
(69,63,'Econometric Analysis',1900,'en',NULL,NULL,1,4,NULL),
(70,64,'Learning OpenCV',1900,'en',NULL,NULL,1,4,NULL),
(71,65,'Data Structures Using C & C++',1900,'en',NULL,NULL,1,4,NULL),
(72,66,'A Modern Approach Computer Vision',1900,'en',NULL,NULL,1,4,NULL),
(73,67,'Principles of Communication Systems',1900,'en',NULL,NULL,1,4,NULL),
(74,68,'Let Us C',1900,'en',NULL,NULL,1,4,NULL),
(75,69,'The Amulet of Samarkand',1900,'en',NULL,NULL,1,4,NULL),
(76,70,'Crime and Punishment',1900,'en',NULL,NULL,1,4,NULL),
(77,71,'Angels & Demons',1900,'en',NULL,NULL,1,4,NULL),
(78,72,'The Argumentative Indian',1900,'en',NULL,NULL,1,4,NULL),
(79,73,'Sea of Poppies',1900,'en',NULL,NULL,1,4,NULL),
(80,72,'The Idea of Justice',1900,'en',NULL,NULL,1,4,NULL),
(81,75,'A Raisin in the Sun',1900,'en',NULL,NULL,1,4,NULL),
(82,76,'All the President's Men',1900,'en',NULL,NULL,1,4,NULL),
(83,43,'A Prisoner of Birth',1900,'en',NULL,NULL,1,4,NULL),
(84,78,'Scoop!',1900,'en',NULL,NULL,1,4,NULL),
(85,79,'Ahe Manohar Tari',1900,'en',NULL,NULL,1,4,NULL),
(86,80,'The Last Mughal',1900,'en',NULL,NULL,1,4,NULL),
(87,81,'Vol 39 No. 1 Social Choice & Welfare',1900,'en',NULL,NULL,1,4,NULL),
(88,52,'Radiowaril Bhashane & Shrutika',1900,'en',NULL,NULL,1,4,NULL),
(89,52,'Gun Gayin Awadi',1900,'en',NULL,NULL,1,4,NULL),
(90,52,'Aghal Paghal',1900,'en',NULL,NULL,1,4,NULL),
(91,85,'Maqta-e-Ghalib',1900,'en',NULL,NULL,1,4,NULL),
(92,86,'Manasa',1900,'en',NULL,NULL,1,4,NULL),
(93,87,'India from Midnight to Milennium',1900,'en',NULL,NULL,1,4,NULL),
(94,87,'The Great Indian Novel',1900,'en',NULL,NULL,1,4,NULL),
(95,89,'O Jerusalem!',1900,'en',NULL,NULL,1,4,NULL),
(96,89,'The City of Joy',1900,'en',NULL,NULL,1,4,NULL),
(97,89,'Freedom at Midnight',1900,'en',NULL,NULL,1,4,NULL),
(98,47,'The Winter of Our Discontent',1900,'en',NULL,NULL,1,4,NULL),
(99,93,'On Education',1900,'en',NULL,NULL,1,4,NULL),
(100,94,'Free Will',1900,'en',NULL,NULL,1,4,NULL),
(101,87,'Bookless in Baghdad',1900,'en',NULL,NULL,1,4,NULL),
(102,96,'The Case of the Lame Canary',1900,'en',NULL,NULL,1,4,NULL),
(103,9,'The Theory of Everything',1900,'en',NULL,NULL,1,4,NULL),
(104,98,'New Markets & Other Essays',1900,'en',NULL,NULL,1,4,NULL),
(105,99,'Electric Universe',1900,'en',NULL,NULL,1,4,NULL),
(106,100,'The Hunchback of Notre Dame',1900,'en',NULL,NULL,1,4,NULL),
(107,47,'Burning Bright',1900,'en',NULL,NULL,1,4,NULL),
(108,98,'The Age of Discontuinity',1900,'en',NULL,NULL,1,4,NULL),
(109,103,'Doctor in the Nude',1900,'en',NULL,NULL,1,4,NULL),
(110,104,'Down and Out in Paris & London',1900,'en',NULL,NULL,1,4,NULL),
(111,72,'Identity & Violence',1900,'en',NULL,NULL,1,4,NULL),
(112,80,'Beyond the Three Seas',1900,'en',NULL,NULL,1,4,NULL),
(113,107,'Talking Straight',1900,'en',NULL,NULL,1,4,NULL),
(114,108,'Vol 3 Maughams Collected Short Stories',1900,'en',NULL,NULL,1,4,NULL),
(115,42,'The Phantom of Manhattan',1900,'en',NULL,NULL,1,4,NULL),
(116,108,'Ashenden of The British Agent',1900,'en',NULL,NULL,1,4,NULL),
(117,111,'Zen & The Art of Motorcycle Maintenance',1900,'en',NULL,NULL,1,4,NULL),
(118,112,'The Great War for Civilization',1900,'en',NULL,NULL,1,4,NULL),
(119,45,'We the Living',1900,'en',NULL,NULL,1,4,NULL),
(120,114,'The Artist and the Mathematician',1900,'en',NULL,NULL,1,4,NULL),
(121,93,'History of Western Philosophy',1900,'en',NULL,NULL,1,4,NULL),
(122,72,'Rationality & Freedom',1900,'en',NULL,NULL,1,4,NULL),
(123,117,'Clash of Civilizations and Remaking of the World Order',1900,'en',NULL,NULL,1,4,NULL),
(124,39,'Uncommon Wisdom',1900,'en',NULL,NULL,1,4,NULL),
(125,119,'One',1900,'en',NULL,NULL,1,4,NULL),
(126,120,'To Sir With Love',1900,'en',NULL,NULL,1,4,NULL),
(127,121,'Half A Life',1900,'en',NULL,NULL,1,4,NULL),
(128,122,'The Discovery of India',1900,'en',NULL,NULL,1,4,NULL),
(129,52,'Apulki',1900,'en',NULL,NULL,1,4,NULL),
(130,93,'Unpopular Essays',1900,'en',NULL,NULL,1,4,NULL),
(131,42,'The Deceiver',1900,'en',NULL,NULL,1,4,NULL),
(132,76,'Veil: Secret Wars of the CIA',1900,'en',NULL,NULL,1,4,NULL),
(133,52,'Char Shabda',1900,'en',NULL,NULL,1,4,NULL),
(134,128,'Rosy is My Relative',1900,'en',NULL,NULL,1,4,NULL),
(135,108,'The Moon and Sixpence',1900,'en',NULL,NULL,1,4,NULL),
(136,130,'A Short History of the World',1900,'en',NULL,NULL,1,4,NULL),
(137,108,'The Trembling of a Leaf',1900,'en',NULL,NULL,1,4,NULL),
(138,103,'Doctor on the Brain',1900,'en',NULL,NULL,1,4,NULL),
(139,133,'Simpsons & Their Mathematical Secrets',1900,'en',NULL,NULL,1,4,NULL),
(140,134,'Pattern Classification',1900,'en',NULL,NULL,1,4,NULL),
(141,135,'From Beirut to Jerusalem',1900,'en',NULL,NULL,1,4,NULL),
(142,133,'The Code Book',1900,'en',NULL,NULL,1,4,NULL),
(143,112,'The Age of the Warrior',1900,'en',NULL,NULL,1,4,NULL),
(144,138,'The Numbers Behind Numb3rs',1900,'en',NULL,NULL,1,4,NULL),
(145,47,'A Life in Letters',1900,'en',NULL,NULL,1,4,NULL),
(146,140,'The Information',1900,'en',NULL,NULL,1,4,NULL),
(147,141,'Elements of Information Theory',1900,'en',NULL,NULL,1,4,NULL),
(148,142,'Power Electronics - Rashid',1900,'en',NULL,NULL,1,4,NULL),
(149,143,'Power Electronics - Mohan',1900,'en',NULL,NULL,1,4,NULL),
(150,144,'Neural Networks',1900,'en',NULL,NULL,1,4,NULL),
(151,47,'The Grapes of Wrath',1900,'en',NULL,NULL,1,4,NULL),
(152,52,'Vyakti ani Valli',1900,'en',NULL,NULL,1,4,NULL),
(153,12,'Statistical Learning Theory',1900,'en',NULL,NULL,1,4,NULL),
(154,148,'Empire of the Mughal - The Tainted Throne',1900,'en',NULL,NULL,1,4,NULL),
(155,148,'Empire of the Mughal - Brothers at War',1900,'en',NULL,NULL,1,4,NULL),
(156,148,'Empire of the Mughal - Ruler of the World',1900,'en',NULL,NULL,1,4,NULL),
(157,148,'Empire of the Mughal - The Serpent's Tooth',1900,'en',NULL,NULL,1,4,NULL),
(158,148,'Empire of the Mughal - Raiders from the North',1900,'en',NULL,NULL,1,4,NULL),
(159,153,'Mossad',1900,'en',NULL,NULL,1,4,NULL),
(160,154,'Jim Corbett Omnibus',1900,'en',NULL,NULL,1,4,NULL),
(161,155,'20000 Leagues Under the Sea',1900,'en',NULL,NULL,1,4,NULL),
(162,156,'Batatyachi Chal',1900,'en',NULL,NULL,1,4,NULL),
(163,156,'Hafasavnuk',1900,'en',NULL,NULL,1,4,NULL),
(164,156,'Urlasurla',1900,'en',NULL,NULL,1,4,NULL),
(165,68,'Pointers in C',1900,'en',NULL,NULL,1,4,NULL),
(166,160,'The Cathedral and the Bazaar',1900,'en',NULL,NULL,1,4,NULL),
(167,161,'Design with OpAmps',1900,'en',NULL,NULL,1,4,NULL),
(168,162,'Think Complexity',1900,'en',NULL,NULL,1,4,NULL),
(169,163,'The Devil's Advocate',1900,'en',NULL,NULL,1,4,NULL),
(170,45,'Ayn Rand Answers',1900,'en',NULL,NULL,1,4,NULL),
(171,45,'Philosophy: Who Needs It',1900,'en',NULL,NULL,1,4,NULL),
(172,166,'Data Analysis with Open Source Tools',1900,'en',NULL,NULL,1,4,NULL),
(173,167,'Broca's Brain',1900,'en',NULL,NULL,1,4,NULL),
(174,168,'Men of Mathematics',1900,'en',NULL,NULL,1,4,NULL),
(175,169,'Oxford book of Modern Science Writing',1900,'en',NULL,NULL,1,4,NULL),
(176,170,'Judiciary and Democracy Justice',1900,'en',NULL,NULL,1,4,NULL),
(177,171,'The Arthashastra',1900,'en',NULL,NULL,1,4,NULL),
(178,172,'We the People',1900,'en',NULL,NULL,1,4,NULL),
(179,172,'We the Nation',1900,'en',NULL,NULL,1,4,NULL),
(180,174,'The Courtroom Genius',1900,'en',NULL,NULL,1,4,NULL),
(181,175,'Dongri to Dubai',1900,'en',NULL,NULL,1,4,NULL),
(182,176,'Foundation History of England',1900,'en',NULL,NULL,1,4,NULL),
(183,80,'City of Djinns',1900,'en',NULL,NULL,1,4,NULL),
(184,178,'India's Legal System',1900,'en',NULL,NULL,1,4,NULL),
(185,179,'More Tears to Cry',1900,'en',NULL,NULL,1,4,NULL),
(186,180,'The Ropemaker',1900,'en',NULL,NULL,1,4,NULL),
(188,182,'The Prince',1900,'en',NULL,NULL,1,4,NULL),
(189,183,'Eyeless in Gaza',1900,'en',NULL,NULL,1,4,NULL),
(190,184,'Tales of Beedle the Bard',1900,'en',NULL,NULL,1,4,NULL),
(191,185,'Girl with the Dragon Tattoo',1900,'en',NULL,NULL,1,4,NULL),
(192,185,'Girl who kicked the Hornet's Nest',1900,'en',NULL,NULL,1,4,NULL),
(193,185,'Girl who played with Fire',1900,'en',NULL,NULL,1,4,NULL),
(194,28,'Structure and Randomness',1900,'en',NULL,NULL,1,4,NULL),
(195,189,'Image Processing with MATLAB',1900,'en',NULL,NULL,1,4,NULL),
(196,104,'Animal Farm',1900,'en',NULL,NULL,1,4,NULL),
(197,70,'The Idiot',1900,'en',NULL,NULL,1,4,NULL),
(198,192,'A Christmas Carol',1900,'en',NULL,NULL,1,4,NULL);

-- Insercion de clientes

INSERT INTO `clients`(name, email, birthdate, gender, active) VALUES ('Maria Dolores Gomez','Maria Dolores.95983222J@random.names','1971-06-06','F',1),
('Adrian Fernandez','Adrian.55818851J@random.names','1970-04-09','M',1),
('Maria Luisa Marin','Maria Luisa.83726282A@random.names','1957-07-30','F',1),
('Pedro Sanchez','Pedro.78522059J@random.names','1992-01-31','M',1),
('Pablo Saavedra','Pablo.93733268B@random.names','1960-07-21','M',1),
('Marta Carrillo','Marta.55741882W@random.names','1981-06-15','F',1),
('Javier Barrio','Javier.54966248C@random.names','1971-04-24','M',1),
('Milagros Garcia','Milagros.90074144A@random.names','1964-12-05','F',1),
('Carlos Quiros','Carlos.63291957M@random.names','1954-08-28','M',1),
('Carmen De la Torre','Carmen.57408761W@random.names','1966-05-14','F',1),
('Laura Moron','Laura.57774589S@random.names','1954-03-02','F',1),
('Maria Dolores Larrea','Maria Dolores.71788005Z@random.names','1990-09-04','F',1),
('Maria Dolores Sanz','Maria Dolores.30948169J@random.names','2001-08-30','F',1),
('Jose Maria Bermudez','Jose Maria.24963969E@random.names','1998-05-23','M',1),
('Carlos Blanco','Carlos.94783133H@random.names','1952-08-07','M',1),
('Juan Carlos Jurado','Juan Carlos.71650477A@random.names','1990-12-12','M',1),
('David Gonzalez','David.54332034P@random.names','1976-05-03','M',1),
('Antonia Aranda','Antonia.91560262E@random.names','1979-10-25','F',1),
('Maria Moreno','Maria.58935447V@random.names','1997-01-12','F',1),
('David Casals','David.06746883V@random.names','1999-07-13','M',1),
('Mario Romero','Mario.46091382A@random.names','1985-03-29','M',1),
('Maria angeles Alba','Maria angeles.91808919A@random.names','1959-09-14','F',1),
('Rafael Espinola','Rafael.67605541P@random.names','1998-10-11','M',1),
('Montserrat alvarez','Montserrat.31114289G@random.names','1994-11-06','F',1),
('Maria Carmen Gomez','Maria Carmen.64351051H@random.names','1968-06-30','F',1),
('Maria Cruz Morillas','Maria Cruz.81385695B@random.names','1978-10-29','F',1),
('Josefa Roldan','Josefa.51417560W@random.names','1993-08-09','F',1),
('Monica Pla','Monica.18992324M@random.names','1972-06-08','F',1),
('Juana Maria Lopez','Juana Maria.51072683X@random.names','1990-07-15','F',1),
('Maria Carmen Ponce','Maria Carmen.41619980P@random.names','1984-07-26','F',1),
('Juan Carlos Rios','Juan Carlos.45673504N@random.names','1967-05-04','M',1),
('Isabel Alfaro','Isabel.77316882J@random.names','1980-07-25','F',1),
('Maria Isabel Armas','Maria Isabel.42010289F@random.names','1950-11-21','F',1),
('Maria Teresa Castillo','Maria Teresa.91228389Q@random.names','2002-11-08','F',1),
('Andres Planells','Andres.09981449R@random.names','1992-06-19','M',1),
('Silvia Perez','Silvia.91812407H@random.names','1969-02-15','F',1),
('Pablo Gonzalez','Pablo.11605676Z@random.names','2000-10-11','M',1),
('Maria Antonia Jimenez','Maria Antonia.98071058R@random.names','1998-06-23','F',1),
('Jesus Rodriguez','Jesus.86679475L@random.names','1961-01-17','M',1),
('Carmen Rodriguez','Carmen.81799536J@random.names','1973-02-17','F',1),
('Maria Dolores Rodriguez','Maria Dolores.75444599E@random.names','1962-08-14','F',1),
('Jordi Campos','Jordi.76000917Q@random.names','1956-09-23','M',1),
('Carlos Caceres','Carlos.97628163V@random.names','1993-05-16','M',1),
('Carmen Robles','Carmen.29258188A@random.names','1955-06-19','F',1),
('Sara Rodriguez','Sara.16181250Z@random.names','2001-06-07','F',1),
('Maria Carmen Rivera','Maria Carmen.59955426S@random.names','2000-05-27','F',1),
('Alberto Cabanas','Alberto.40633755T@random.names','1991-10-27','M',1),
('Jose Sanchez','Jose.52243847Z@random.names','1976-12-06','M',1),
('Isabel Martinez','Isabel.90843261T@random.names','1962-07-01','F',1),
('David Sanchez','David.14910073R@random.names','1975-05-18','M',1),
('Sergio Sebastian','Sergio.09345984A@random.names','1959-08-30','M',1),
('Manuel Cabrera','Manuel.38738750B@random.names','1993-08-23','M',1),
('Marina Gabaldon','Marina.12101665P@random.names','1959-03-25','F',1),
('Rafael Galvez','Rafael.87947175M@random.names','1988-09-02','M',1),
('Francisco Villar','Francisco.13922268T@random.names','1952-04-25','M',1),
('Francisco Garcia','Francisco.34242509V@random.names','1989-01-22','M',1),
('Esther Pina','Esther.36300729J@random.names','1977-11-07','F',1),
('Maria Jesus Noya','Maria Jesus.95839533M@random.names','1996-08-07','F',1),
('Paula Ropero','Paula.53630073F@random.names','1998-09-04','F',1),
('Maria Carmen Iglesias','Maria Carmen.24044144J@random.names','1977-06-12','F',1),
('Albert Galvez','Albert.10067957Y@random.names','1971-05-17','M',1),
('Carmen Lopez','Carmen.09399409E@random.names','1987-03-07','F',1),
('Francisco Jose Leon','Francisco Jose.07598657D@random.names','1965-12-11','M',1),
('Francisca Gonzalez','Francisca.19675393C@random.names','1957-12-23','F',1),
('Daniel Garcia','Daniel.01386486T@random.names','1979-05-29','M',1),
('Ana Maria Martinez','Ana Maria.91340418N@random.names','1980-09-14','F',1),
('Maria Aguilar','Maria.41749884P@random.names','2000-07-31','F',1),
('oscar Penas','oscar.31681177B@random.names','1981-10-02','M',1),
('Adrian Vela','Adrian.66561884E@random.names','1978-12-10','M',1),
('Francisco Alcalde','Francisco.52899588W@random.names','1967-03-11','M',1),
('Maria Dolores Perez','Maria Dolores.47800073R@random.names','2003-11-10','F',1),
('Juan Jose Tejada','Juan Jose.30429668R@random.names','1990-06-15','M',1),
('Cristobal Nogues','Cristobal.01001763K@random.names','2003-10-01','M',1),
('Maria Luisa Sanchez','Maria Luisa.91748033K@random.names','2000-02-03','F',1),
('Adrian Orta','Adrian.11458937S@random.names','1952-03-20','M',1),
('Maria Pilar Martin','Maria Pilar.93607154Y@random.names','1996-08-29','F',1),
('Jesus Perez','Jesus.91931655B@random.names','1954-06-01','M',1),
('Jesus Perez','Jesus.15757299E@random.names','1956-08-29','M',1),
('Esther Capdevila','Esther.96440550D@random.names','1970-10-12','F',1),
('David Nieves','David.40697907M@random.names','1965-04-02','M',1),
('Antonia Giron','Antonia.32080105G@random.names','1983-01-22','F',1),
('Juan Casero','Juan.94063877H@random.names','1974-06-29','M',1),
('Manuel De Pablo','Manuel.01279669H@random.names','1973-03-23','M',1),
('angel Palomo','angel.28890315S@random.names','1991-07-04','M',1),
('Laura Herrera','Laura.98555932N@random.names','1966-12-12','F',1),
('Maria Josefa Benitez','Maria Josefa.36743977M@random.names','1987-04-17','F',1),
('Luis Saez','Luis.08103734Y@random.names','1983-03-28','M',1),
('Susana Nevado','Susana.09442372K@random.names','1961-12-26','F',1),
('Miguel Gomez','Miguel.01631964E@random.names','1965-05-16','M',1),
('Julio Mayordomo','Julio.77582185B@random.names','1968-06-05','M',1),
('Sonia Mari','Sonia.06246888L@random.names','1994-10-13','F',1),
('Antonia Beltran','Antonia.96371304Q@random.names','1967-11-17','F',1),
('Mercedes Perez','Mercedes.80944345P@random.names','1958-11-05','F',1),
('Concepcion Velez','Concepcion.56896097P@random.names','1964-04-05','F',1),
('Diego Correa','Diego.44862413Q@random.names','1999-09-15','M',1),
('Juan Antonio Galan','Juan Antonio.95710220K@random.names','1982-11-20','M',1),
('Manuel Cerezo','Manuel.25853412D@random.names','1991-03-12','M',1),
('Rosa Maria Singh','Rosa Maria.41642169W@random.names','1956-12-31','F',1),
('Angeles Mena','angeles.88859550Q@random.names','1987-09-22','F',1),
('Jose Hidalgo','Jose.05903641R@random.names','1973-08-13','M',1);

-- Comando on duplicate 
/* 🚫Nunca usa ON DUPLICATE KEY IGNORE ALL⛔
PROHIBIDISIMOOOOOOOOOOOOOOOOOOOO */

INSERT INTO clients(name, email, birthdate, gender, active) 
VALUES('Royer G', 'royjuni3431@gmail.com', '2002-02-16', 'M', 0)
ON DUPLICATE KEY IGNORE All;

INSERT INTO clients(name, email, birthdate, gender, active) 
VALUES('Royer G', 'royjuni3431@gmail.com', '2002-02-16', 'M', 0)
ON DUPLICATE KEY UPDATE active = VALUES(active);

-- Truco para la presentacion de los datos \G (Mas legible)
SELECT * FROM clients WHERE client_id = 101\G
SELECT * FROM clients\G

Luis Higuera

Hace 6 años

Men muchas gracias, toma tu like!

Alexander Velasco

Hace 6 años

Como has puesto todo el script? Solo puedo compratir una imagen

Introducción

MySQL directo en consola con Alberto

Instalación de MySQL Server en Windows

Instalando MySQL en Mac OS

Instalación local de un RDBMS (Ubuntu)

Conectar MySQL desde terminal con comandos seguros

Qué es una base de datos relacional

CREATE

Tipos de datos MySQL para tablas rápidas

Tipos de columnas / Creación de la tabla books

Crear tablas con relaciones lógicas en MySQL

Crear tabla clients: tipos de datos avanzados

INSERT

INSERT en MySQL: cómo evitar duplicados

ON DUPLICATE KEY en MySQL práctico

INSERT con subquery para authors

Bash y archivos SQL

Cómo importar archivos SQL con MySQL

SELECT

Funciones YEAR, WHERE y LIKE en MySQL SELECT

Comando JOIN

Inner vs left join en SQL

Tipos de JOIN

Traductor de preguntas de negocio a SQL

Continuación de casos de negocio

MySQL UPDATE y DELETE sin riesgos

Consultas en MySQL

Cómo crear matrices con SUM(IF) en MySQL

Modificar tablas con ALTER TABLE en MySQL

Contenido Bonus

MySQL en terminal: datos a decisiones

ON DUPLICATE KEY en MySQL práctico