Sometimes, when searching for an answer, we end up making things too much complicated, while easy solutions are just round the corner. This is the case of a simple task like updating two related tables with just one SQL query.
Suppose we have two related tables. The first contains user names, and the second email addresses related to the first table names.
First table ("names")
The second table ("addresses")
How do we change the name and the street of the first record (with id equal to 1)?
With one simple query.
The queryWhen we need to update one table, we usually perform a single SQL query like:
Simple as that.
name = 'Peter'
ID = 1
But if we want to update the two tables at the same time?
We can use a JOIN based on the ID field.
names.ID = addresses.ID
names.name = 'Peter', addresses.address = 'Third Street'
names.ID = 1
The advantages?Well, there is an immediate advantage in performing just a single SQL query instead of two, and I believe it is quite clear: the server will have a lighter work load.
At the same time, we will have a full control on the performed operation, which will be faster and easier to maintain.
Sometimes, solutions are easier than thought, and I believe this is the case.