Blog

SQL Update records from one table to another

Has the need arisen for you to update records by crossing the information between tables? If so, you’re in the right place; In this tutorial I am going to explain in an exemplary way how to use an UPDATE SQL to update the records from another table.

From now on I am going to call this kind of UPDATE as “UPDATE SELECT”.

So here are 3 ways to learn this concept:

  • A description of the query to perform.
  • Examples of this “UPDATE SELECT”.
  • UPDATE with JOIN and added records.
  • An explanatory video on how it is used and the results to be obtained.

Also, the following queries have been tested by myself on MariaDB/MySQL.

Content

  • Update from a Select MySQL
    • Example of Update with Join
    • UPDATE and INNER JOIN example 2
  • UPDATE SQL with JOIN from aggregated records
    • UPDATE with JOIN and an ALIAS
  • Video example UPDATE with JOIN in MySQL
  • Final on UPDATE JOIN and added records

Update from a Select MySQL

This type of UPDATE becomes really powerful when we know how to use it. In theoretical terms we could say that it is a kind of UPDATE with JOIN.

To carry out this query we will use the syntax of an SQL UPDATE mixed with a SELECT. As? Well, in the following way.

Example of Update with Join

In this example you will be able to see the general structure for any simple UPDATE JOIN you can imagine, that is, an update query involving two tables.

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

What happened here? This query is a mix between an UPDATE and a SELECT, in the way that it starts as an UPDATE and is extended with the final syntax of a SELECT.

In this way we have managed to make a JOIN between the table “table1” and the table “table2”.

Thanks to this JOIN we are assigning to any field of “table1” (the one that belongs to the UPDATE) the value of any other column of the table “table2”.

UPDATE and INNER JOIN example 2

Another way to perform this same query would be using the JOIN clause directly.

Just as in a SELECT we can join by JOINS all the tables we need, we could also do it here.

Here is the example equivalent to the previous update sql query but using the INNER JOIN clause.

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

To this last example we could also add a WHERE clause, but so that you can see the equivalent example I have not used it.

UPDATE SQL with JOIN from aggregated records

When you’ve used this query a few times or fully understand it, you realize that it could be a great way to update records with records added from other tables.

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.

For example, imagine we have two tables, and one of them has a column that is the sum of all the others. So that you can visualize it more easily, I am going to create two tables that describe themselves:

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)
)

As you will see, these two tables could be part of any database of an online store.

Basically, for what we are interested in, we have a column “total_compras” which is the sum of the records in the “purchase” table.

Thus, with what we have learned above, we could try to make the following query MISTAKENLY:

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

This query fails, producing an error saying: Aggregate record data cannot be used in an UPDATE. In MySQL in particular the following error occurs: SQL error (1111): Invalid use of group function.

To perform this functionality we should do is create a VIEW or an ALIAS that returns to the UPDATE the result of the aggregation of the second table. In this way the JOIN the UPDATE find in this view a single record for each record of the table to be updated.

UPDATE with JOIN and an ALIAS

Now we are going to see the correct way of this problem with the aggregate records.

First let’s look at the ALIAS or SELECT that we would need to achieve the above correct UPDATE:

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

And now with this query we are going to do the JOIN of the 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;

It is a complex query that may require a couple of readings to fully understand it, but I assure you that once you understand it, it is no more secret than any other query with subqueries.

Video example UPDATE with JOIN in MySQL

In the following video you can see an explanation through examples of SQL UPDATE and JOIN in MySQL and using PhpMyAdmin.

Final on UPDATE JOIN and added records

Was over! I hope that it has been helpful to you in your work and that everything has been made quite clear. If so, I would appreciate it if you leave a comment, contribute your conclusions and share this article. This will help me to keep motivated creating this content and other users to choose the correct answer to their questions.

Thank you!

Leave a Reply