使用MySQL使用左连接、变量和自动增量更新查询

Question

我被困在一个情况下。这是表结构。

default_category

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

default_products

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    

default_products_code

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

下面是详细信息

每当创建类别时，我都会在default_products_code中输入一个条目，并提供code =1作为默认值。现在，每当插入产品时，我从default_products_code获得代码，从default_category获得symbol，并生成如下代码，例如，产品A的代码是SMT0001，然后我将default_products_code中的代码更新为2，因为product_code中已分配了1。这就是我目前的系统。但现在问题来了，我的数据库中有大量的产品，我必须用我解释的上述条件更新新添加的列。

这是我尝试使用自动增量的查询

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

另一件事，我需要更新表中的所有产品。我知道在这种情况下我需要删除subcategory_id =1的位置，但我无法继续。

Answer 1

嗯，经过一些修改，我已经找到了怎么做。

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条件的情况下更新它，这样我就可以更新所有产品。

Answer 2

你的问题出在LPAD上

LPAD的定义是这样的

   LPAD(str, len, padstr) 

在您的查询中，您应该这样使用

 LPAD(id, 5, 0) 

在您的查询LPAD(@var,5,'0')中，由于@var为1，它将始终返回00001