MySql > update data with multiple tables in SQL mode

In layman’s terms, an update operation requires updating the target table with data from the associated table.

According to the custom of use, whether in Navicat or Java background, you need to split into at least two SQL operations.

Since this type of requirement is performed occasionally in DB, it is not possible to run the background every time; It is also impossible to manually query the target data each time and then modify the UPDATE statement to execute it

Table structure

A classification table mdM_classification, where the parent child will be used to combine the data mapping of the primary, secondary and tertiary classification, the corresponding classification code length is 2 bits, 4 bits, 7 bits respectively, where the naming style is: Aa, AABB, aABBCCC, namely, the first and second level codes can be obtained by intercepting specific three-level codes

A material table, MDM_Basics, will have its own unique code, and bind its own classification by MDM_ClassiFICation_id. And there are redundant fields mdM_product_code1, MDM_product_code2, mdm_product_code3 to bind their corresponding code and name of the first, second, and third categories

scenario

If there is a material with code=1, its bound tertiary classification code is 1301001, which needs to be changed to 2302001. At this time, the primary and secondary classification data of this material also need to be maintained synchronously.

Conventional solutions

A background is needed to intercept the first and second level classification code of 2302001, query it in DB to obtain the corresponding classification record data of code, and execute it in DB after splicing the UPDATE SQL, and update the data into the database

SQL Solution

Auto-join and complete data for the classification table

Complete the corresponding code and name of the first and second level classification for the records of the third level classification that we need to query through the left connection

SELECT
        c3.id c3id,
        c1.mdm_code c1code,
        c1.mdm_name c1name,
        c2.mdm_code c2code,
        c2.mdm_name c2name,
        c3.mdm_code c3code,
        c3.mdm_name c3name
FROM
        mdm_classification c3
        LEFT JOIN mdm_classification c2 ON c2.mdm_code = left(c3.mdm_code,4)
        LEFT JOIN mdm_classification c1 ON c1.mdm_code = left(c3.mdm_code,2)
WHERE
        c3.mdm_code = '2302001' 
Copy the code

The query results are as follows

The returned result record already has all the data we need

League table updates

The template for the update of the table is as follows:

UPDATE 
    tab1 a,
    tab2 b
SET
    a.xx = b.xx [, ...]
[WHEREconditions... ]Copy the code

The core idea is to use the data in table B to update the records in table A

Pay attention to the point

Since table B is the data source to update, we can use temporary tables to query the combination to get the desired data set; Table A is the update body and must be an existing table in the database. If table A also uses A temporary table, mysql will report an error because it cannot locate the location that actually needs to be updated.

The following illustration shows an example of correct action on the left and an example of incorrect use of a temporary table as an update body table on the right:

The finished product SQL

UPDATE 
    mdm_basics b,
    (SELECT
            c3.id c3id,
            c1.mdm_code c1code,
            c1.mdm_name c1name,
            c2.mdm_code c2code,
            c2.mdm_name c2name,
            c3.mdm_code c3code,
            c3.mdm_name c3name 
    FROM
            mdm_classification c3
            LEFT JOIN mdm_classification c2 ON c2.mdm_code = LEFT ( c3.mdm_code, 4 )
            LEFT JOIN mdm_classification c1 ON c1.mdm_code = LEFT ( c3.mdm_code, 2 ) 
    WHERE
            c3.mdm_code = '2302001' 
    ) c 
SET 
    b.mdm_classification_id = c.c3id,
    b.mdm_group = c.c3code,
    b.mdm_product_code3 = c.c3code,
    b.mdm_classification_name = c.c3name,
    b.mdm_product_name3 = c.c3name,
    b.mdm_product_code1 = c.c1code,
    b.mdm_product_name1 = c.c1name,
    b.mdm_product_code2 = c.c2code,
    b.mdm_product_name2 = c.c2name,
    b.update_date = now() 
WHERE
    b.mdm_code IN (
        '20031500' 
    )
Copy the code

conclusion

Mysql is more powerful than we thought, and CURD, which accounts for most of our daily development work, is just the tip of the iceberg. Don’t let our conventional thinking limit our exploration of mysql’s capabilities

Off topic: Unlocked abilities

These are, in DB, problems solved by SQL:

Highly aggregated data items are split into multiple rows and columns. SQL is used to split data that is combined according to specific delimiters and specific formats

Complex trigger case sharing, through the trigger, to achieve logging, and the value of the changed field before and after the value

Multiple rows of data are converted into multiple columns in the same row. Through SQL, the dimension of data displayed in DB is transformed from rows to columns

Batch update, using mysql features, 1 SQL to solve the thousands of data update (each data change point is different)

Original article, without permission, prohibit reprinting

-Leonard: Create by the comfort of salt fish