How to do an UPDATE using data from two different tables?

Question

How to do an UPDATE using data from two different tables?

Navigation

#1 by (4 votes)
#2 by (1 votes)

1

The table was once:

Id   dado1  dado2  
01   1000   10  
02   1234   12  
03   9999   1

I had a cron task that ran the following update daily:

UPDATE tabela1 SET dado1 = dado1 + dado2;

Just to organize things, move dado2 to another table.

Then I tried:

UPDATE tabela1 SET dado1 = dado1 + tabela2.dado2;

But it did not work.

The two tables have id , but how do I use id so that each row of UPDATE in tabela1 uses dado2 of its id ?

mysql sql-update

asked by anonymous 05.11.2016 / 00:11

2 answers

1

To make manipulations with more than one table use the command JOIN :

UPDATE tabela1 JOIN tabela2 SET dado1 = dado1 + tabela2.dado2;

05.11.2016 / 00:18

List files in a folder that contain a specific word in the name Click function and block inputs

score 4 · Accepted Answer

You need to merge the two by the common key of both:

 UPDATE     tabela1
 INNER JOIN tabela2 ON tabela1.id = tabela2.id
 SET        tabela1.dado1 = tabela1.dado1 + tabela2.dado2;

See working on SQL Fiddle .

It is important to know that in the ON clause the condition that joins the two tables must be specified. Note that the fields have different names in each table, so you do not flip ON or forget to adjust either side.

If you want, before moving the table, test ON with SELECT to see if everything is in order before modifying the data:

 SELECT *
 FROM tabela1
 INNER JOIN tabela2 ON tabela1.id = tabela2.id;

We chose INNER JOIN because we are updating tabela1 with data from tabela2 , so we are not interested in lines where there is no match between tables.

Important: We are assuming that the relation is 1: 1. If you have repeated IDs in any of the tables, the value will be added more than once (but there is an architecture decision, not a query problem specifically).

To better understand which JOIN use in each case, see this post:

What's the difference between INNER JOIN and OUTER JOIN?