UPDATE of two or more tables

Using the UPDATE command, we can act on existing records and modified.
Suppose, for example, have, in our DB table phone_numbers structured as follows:

• name
• telephone

and say that our. Table 3 is already populated with records:

name	telephone
John Smith	123.456789
Antonio Bianchi	987.654321
Claudio Verdi	321.654987

Now let's say that Mr. John Smith has been transferred and, therefore, has changed the phone number. To upgrade our phone book we use a query like this:

 UPDATE







 phone_numbers







 September







 Phone = '213 .698547 '







 WHERE







 name = 'John Doe'

So far nothing new.
Now suppose we also have another table called addresses the following structure:

• name
• by
• city
• prov

Suppose then that this table is already populated with 3 records corresponding to the addresses of three friends already present in the other table:

name	by	cites	prov
John Smith	Via Roma 11	Turin	TO
Antonio Bianchi	Via Garibaldi 2	Gallarate	VA
Claudio Verdi	Piazza Cavour 4	Como	CO

After this long preamble we come to the question that the title to this article: How can I update two tables simultaneously, ie using a single query instead of two?
Simple, using a JOIN in the UPDATE phase!

Returning to the first we use a query like this:

 UPDATE







 phone_numbers







 INNER JOIN







 addresses







 ON







 numeri_di_telefono.nome = indirizzi.nome







 September







 numeri_di_telefono.telefono = '213 .698547 ',







 indirizzi.via = 'Square of the Republic 5',







 indirizzi.citta = 'Milan',







 indirizzi.prov = 'MI'







 WHERE







 numeri_di_telefono.nome = 'John Doe'

After executing the query UPDATE verified by two separate SELECT on two tables. Here are their results (highlighted in yellow):

1) Table phone_numbers

 



 SELECT * FROM phone_numbers

 

name	telephone
John Smith	213.698547
Antonio Bianchi	987.654321
Claudio Verdi	321.654987

1) address table

 



 SELECT * FROM addresses

 

name	by	cites	prov
John Smith	Square of the Republic 5	Milan	MI
Antonio Bianchi	Via Garibaldi 2	Gallarate	VA
Claudio Verdi	Piazza Cavour 4	Como	CO

The practical advantage of a technique to update multiple tables, each related by JOIN is to reduce the workload of the server based on a simple principle: "a query is better than two."