SQL Actualizar registros de una tabla a otra

¿Te ha surgido la necesidad de actualizar registros cruzando la información entre tablas? Si es así estás en el lugar indicado; en este tutorial voy a explicar de forma ejemplificada como utilizar un UPDATE SQL para actualizar los registros desde otra tabla.

En adelante voy a llamar a esta suerte de UPDATE como "UPDATE SELECT".

Así que a continuación te propongo 3 formas de aprender este concepto:

• Una descripción de la consulta a realizar.
• Ejemplos de este "UPDATE SELECT".
• UPDATE con JOIN y registros agregados.
• Un video explicativo sobre como se usa y los resultados a obtener.

Además, las siguientes consultas las he probado yo mismo en MariaDB/MySQL.

Update a partir de una Select MySQL

Este tipo de UPDATE se vuelve realmente potente cuando conocemos la forma de usarlo. En términos teóricos podríamos decir que es una especie de UPDATE con JOIN.

Para realizar esta consulta utilizaremos la sintaxis de un UPDATE de SQL mezclada con un SELECT. ¿Cómo? Pues de la siguiente forma.

Ejemplo de Update con Join

En este ejemplo vas a poder ver la estructura general para cualquier UPDATE JOIN sencillo que imagines, es decir, una consulta de actualización en la que intervengan dos tablas.

UPDATE tabla1 a, tabla2 b
SET tabla1_campo1 = b.tabla2_campo1
WHERE a.campo_x = b.campo_x

¿Que ha pasado aquí? Esta consulta es una mezcla entre un UPDATE y un SELECT, de la forma que empieza como un UPDATE y se extiende con la sintaxis final de un SELECT.

De esta forma hemos logrado hacer un JOIN entre la tabla "tabla1" y la tabla "tabla2".

Gracias a este JOIN estamos asignando a un campo cualquier de la "tabla1" (la que pertenece al UPDATE) el valor de otra columna cualquiera de la tala "tabla2".

Ejemplo 2 de UPDATE y INNER JOIN

Otra forma de realizar esta misma query sería utilizando directamente la cláusula JOIN.

Así como en un SELECT podemos unir mediante JOINS todas las tablas que necesitemos, también podríamos hacerlo aquí.

A continuación te dejo el ejemplo equivalente a la consulta update sql anterior pero utilizando la cláusula INNER JOIN.

UPDATE 
  tabla1 a INNER JOIN tabla2 b  ON a.campo_x = b.campo_x
SET
  tabla1_campo1 = b.tabla2_campo1;

A este último ejemplo podríamos añadirle también una cláusula WHERE, pero para que vieras el ejemplo equivalente no la he utilizado.

UPDATE SQL con JOIN a partir registros agregados

Cuando uno ya ha utilizado esta consulta varias veces o la comprende completamente, se da cuenta de que podría ser una gran manera de actualizar registros con registros agregados de otras tablas.

Registros agregados: son aquellas filas/registros que se crean agrupando datos de otros registros. 
Un ejemplo de estos registros es el resultado de una query con GROUP BY.

Por ejemplo, imagina que tenemos dos tablas, y una de ellas tiene una columna que es la suma de todas otras. Para que lo visualices más fácilmentete voy a crear dos tablas que se describen por si solas:

CREATE TABLE usuario(
	id INT(11) PRIMARY KEY NOT NULL DEFAULT AUTOINCREMENT,
	nombre VARCHAR(100) NOT NULL,
	total_compras INT(11) NOT NULL DEFAULT 0	
);

CREATE TABLE compra(
	id INT(11) PRIMARY KEY NOT NULL DEFAULT AUTOINCREMENT,
        id_usuario INT(11) REFERENCES usuario(id),
	fecha_compra DATE NOT NULL,
	total_dinero DECIMAL(10,2)
)

Como observarás, estas dos tablas podrían formar parte de cualquier base de datos de una tienda online.

Básicamente, para lo que nos interesa, tenemos una columna "total_compras" que es el sumatorio de los registros de la tabla "compra".  

Así, con lo aprendido arriba podríamos intentar hacer ERRONEAMENTE la siguiente consulta:

UPDATE   usuario a INNER JOIN compra b
  ON a.id = b.id_usuario;
SET
  a.total_compras = COUNT(b.id)

Esta consulta es errónea, y produce un error que dice: no se pueden utilizar en un UPDATE datos de registros agregados. En MySQL en concreto se produce el siguiente error: Error de SQL (1111): Invalid use of group function.

Para realizar esta funcionalidad deberíamos hacer es crear una VIEW o un ALIAS que devuelva al UPDATE el resultado de la agregación de la segunda tabla. De esta forma el JOIN del UPDATE encuentre en esta vista un sólo registro para cada registro de la tabla a actualizar.

UPDATE con JOIN y un ALIAS

Ahora vamos a ver la forma correcta de este problema con los registros agregados.

Primero veamos el ALIAS o SELECT que necesitariamos para lograr el anterior UPDATE correcto:

SELECT a.id AS id, COUNT(b.id) as total_compras
FROM usuario a   INNER JOIN compra b  ON a.id = b.id_usuario
GROUP BY a.id

Y ahora con esta consulta vamos a hacer el JOIN del UPDATE:

UPDATE 
usuario a 
	INNER JOIN ( SELECT a.id AS id, COUNT(b.id) as total_compras
					FROM usuario a 
					  INNER JOIN compra b 
					   ON a.id = b.id_usuario
					   GROUP BY a.id
					) tabla2 ON a.id = tabla2.id
SET a.total_compras = tabla2.total_compras;

Es una consulta compleja que tal vez requiera un par de lecturas para su total comprehensión, pero te aseguro que una vez la entiendas no tiene mayor secreto que cualquier otra consulta con subconsultas.

Video ejemplo UPDATE con JOIN en MySQL

En el siguiente video puedes ver una explicación mediante ejemplos de UPDATE SQL y JOIN en MySQL y utilizando PhpMyAdmin.

Final sobre UPDATE JOIN y registros agregados

¡Se terminó! Espero que te haya resultado de ayuda en tu trabajo y haya quedado todo bastante claro. Si es así te agradecería que dejes un comentario, aportes tus conclusiones y compartas este artículo. Esto me ayudará a mi a seguir motivado creando este contenido y a otros usuarios a elegir la respuesta correcta a sus dudas.

¡Gracias!