Alternative usage of MySQL table alias

The MySQL table alias table not only allows SQL statements to be brief, but also supports functions similar to nested queries. Next let's take a look at the alternative usage of MySQL table alias.

MySQL has been used for a long time and has been plagued by the absence of nested queries in MySQL. Although MySQL supports this feature, all my PHP programs use the MySQL function library instead of the MySQLi function library, so 4.1 of the features are not used.

Previously, when nested queries were needed, they were generally converted into join queries for two tables. However, in a recent program, it was found that the nested query is performed twice on the same table, so it cannot be directly converted into join queries for the two tables. It is too troublesome to use PHP instead of MySQL to do such a thing.) So I always wanted to find a simple alternative. Perhaps it was a burst of inspiration. Maybe I recently went through the MySQL manual every day to have a better understanding of MySQL queries. Today I suddenly thought that I could use the table alias to solve this problem.

I used to think that the alias of a table is only used to make the SQL statement brief and has no other use. I discovered it after experiment today, A table can be used as multiple tables with different aliases. Here is an example of my program:

My program has a category table. To achieve unlimited multi-level classification, I Have A parent_id field in the category table. This field stores the parent category ID of the category represented by this record, if this field is null, it indicates the top-level category. Now I need to delete a category. This category may have subcategories. Therefore, after this category is deleted, the parent_id of its subcategories needs to be changed, instead of pointing to a non-existent category, I want to delete this category and change its parent category to its parent category. In this case, I didn't know what to do before. Now, use the following statement:

 
 

  
  
lock tables `categories` write, `categories` as `c1` write,   `categories` as `c2` write;   
  
  
 
  
  
update `categories` as `c1`, `categories` as `c2` set `c1`.`parent_id` = `c2`.`parent_id` where `c1`.`parent_id` = `c2`.`cat_id` and `c2`.`cat_id` = $cat_id;   
  
  
 
  
  
delete from `categories` where `cat_id` = $cat_id;   
  
  
 
  
  
unlock tables;  
  
  
 
 
 

Note that when locking a table, you must lock both the table to be operated and the alias of the table. Otherwise, the following statements may fail.
 

Solution to three types of mysql tabulation garbled data

How to modify the table structure in mysql

Three Common MySQL table creation statements

MySQL connection Query

How to Implement MySQL multi-Table insertion