使用MySQL使用LEFT JOIN、Variable和Autoincrement更新查询

Question

我陷入了困境。这是表格结构。

默认类别

category_id | parent_id |   name                | symbol 
-----------------------------------------------------------
    1       |   1       |   SMT Equipment       |   SMT
    2       |   1       |   ATE & INSPECTION    |   ATE
    3       |   1       |   Feeders             |   FED
    4       |   1       |   Rework Station      |   RWS
    5       |   1       |   X-Ray Machines      |   XRM

默认_产品

id  | subcategory_id | product_name | product_code
---------------------------------------------------
1   |   1           |   A           |   null
2   |   1           |   B           |   null
3   |   2           |   C           |   null
4   |   2           |   D           |   null
5   |   1           |   E           |   null
6   |   3           |   F           |   null
7   |   4           |   G           |   null
8   |   1           |   H           |   null
9   |   2           |   I           |   null    

默认_产品_代码

id  | category_id | code    
-------------------------
1   |       1     | 1   
2   |       2     | 1       
3   |       3     | 1   
4   |       4     | 1       
5   |       5     | 1

下面是的详细信息

每当创建一个类别时，我都会在default_products_code中输入一个条目并提供代码＝1作为默认值。现在插入产品时我从default_products-code获得代码，从defaultcategory获得符号并制作这样的代码，例如产品a的代码为SMT0001然后我将default_products_code中的代码更新为2，因为1已经分配在product_code中。这就是我目前的系统。但现在的问题是我的数据库中有很多产品，我必须更新我解释了新添加的具有上述条件的列。

这是我尝试过的自动递增的查询

SET @var := 1;
UPDATE default_products AS dp
LEFT JOIN(
            SELECT 
                dc.category_id,
                CONCAT(symbol, LPAD(@var,5,'0')) AS `code`,
                @var := @var+1
            FROM default_products AS dp
            LEFT JOIN default_category AS dc ON dc.category_id = dp.subcategory_id
            WHERE subcategory_id = 1
        ) AS l ON l.category_id = dp.subcategory_id 
SET part_code = l.code
WHERE subcategory_id = 1;

它产生这个结果

id  | subcategory_id | product_name | product_code
---------------------------------------------------
1   |   1           |   A           |   SMT00001
2   |   1           |   B           |   SMT00001
5   |   1           |   E           |   SMT00001
8   |   1           |   H           |   SMT00001

而我需要这个

id  | subcategory_id | product_name | product_code
---------------------------------------------------
1   |   1           |   A           |   SMT00001
2   |   1           |   B           |   SMT00002
5   |   1           |   E           |   SMT00003
8   |   1           |   H           |   SMT00004

另一件事是我需要更新表中的所有产品。如何在一个查询中完成此操作。我知道我需要离开在这种情况下，WHERE subcategory_id=1，但我无法继续。

Answer 1

您的问题是LPAD

LPAD的定义类似

   LPAD(str, len, padstr) 

在你的查询中，你应该像一样使用

 LPAD(id, 5, 0) 

在查询LPAD(@var,5,'0')中，它将始终返回00001，因为@var是1

Answer 2

经过一些修改，我找到了如何做到这一点。

SET @var := 0;
UPDATE default_products AS dp
LEFT JOIN(
        SELECT 
        dc.category_id,
        dc.symbol
        FROM default_products AS dp
        LEFT JOIN default_category AS dc ON dc.category_id = dp.subcategory_id
        WHERE subcategory_id = 16
        group by dp.id            
    ) AS l ON l.category_id = dp.subcategory_id 
SET part_code = CONCAT(l.symbol, LPAD(@var := @var+1,5,'0'))
WHERE subcategory_id = 16;

我已经完成了代码创建，它正按照我的需要工作。我仍然想用更新删除了where条件，以便我可以更新所有产品。