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

Question

Once upon a time the table:

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

I had a task cron which performed the following update daily:

UPDATE tabela1 SET dado1 = dado1 + dado2;

Only to organize things, I moved dado2 to another table.

Then I tried:

UPDATE tabela1 SET dado1 = dado1 + tabela2.dado2;

but it didn’t work out.

The two tables have id, but how do I use the id so that each line of the UPDATE in tabela1 use the dado2 of their respective id?

Answer 1

You need to unite 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.

Important to know that in the clause ON the condition joining the two tables must be specified. Note that the fields have different names in each table, not to invert in the ON or forget to adjust any of the sides.

If you want, before you touch the table, test the ON with a 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 why are we updating the tabela1 with data from tabela2, then we are not interested in lines where there is no match between the tables.

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

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

What is the difference between INNER JOIN and OUTER JOIN?

Answer 2

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

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