Delete those tables using JOIN

In this short article we'll see how it is possible to make queries to delete (DELETE) exploiting the potential of the joins, ie, creating relationships between tables in MySQL.
In fact we have already seen how to use the JOIN in the selection of the data (SELECT) and being updated (UPDATE), here we will only extend what has already been learned.

To better learn the potential of this technique should consider, as our habit to assume a concrete case of use.

Let's say we have to work on a database of a hypothetical warehouse consists of the following tables (of which we describe their structures):

• shelves
  • id (int)
  • description (varchar)
• shelves
  • id (int)
  • scaffale_id (int)
  • description (varchar)
• products
  • id (int)
  • ripiano_id (int)
  • name (varchar)
  • price (float)

Now suppose you have the need to eliminate the products. We address the following three different outcomes.

1) Assume you want to delete a single product that you know the ID. In this case you can just run a trivial query:

 



 DELETE * FROM products WHERE id = 1;

 

2) The same query will be used if you want to delete all products packed into a given shelf:

 



 DELETE * FROM products WHERE ripiano_id = 1;

 

3) But if you want to delete all products within a given shelf?
A simple query such as those seen previously would not be possible because in the "products" there is no field that refers to the shelf!

In reality the problem is not insurmountable! with a little 'dexterity in handling queries, in fact, the obstacle is easily circumvented! How? with a JOIN! Here's an example:

 



 DELETE products .*







 FROM products INNER JOIN shelves







 ON = prodotti.ripiano_id ripiani.id







 WHERE ripiani.scaffale_id = 1;

In doing so we created a relationship between two tables "products" and "shelves" (based on the field of JOIN "ripiano_id" table that matches the first identifier of the second) in order to exploit the field "scaffale_id" of this' last.

At first glance, perhaps, might seem a bit complicated ... is actually quite simple. It's about creating a logical relationship between two tables in order to use information to operate the first of the second.

Where, in addition to the products, we wanted to remove all the shelves shelf corresponding to the given pututo we use a slightly different query:

 



 DELETE products .*, shelves .*







 FROM products INNER JOIN shelves







 ON = prodotti.ripiano_id ripiani.id







 WHERE ripiani.scaffale_id = 1;

In essence this is the query view just above the only difference being that the cancellation will affect not only the fields in the table "products":

 



 products .*

 

but also those of the Table "shelf":

 



 shelves .*

 

For questions or requests I invite you to post on our forums .