使用MySQL使用LEFT JOIN，变量和自动增量更新查询 [英] Update Query with LEFT JOIN , Variable and Autoincrement Using MySQL
问题描述
我陷入困境.这是表的结构.
I am stuck on a situation. Here is the table structures.
default_category
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
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
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中输入一个条目 并提供代码= 1作为默认值.现在，无论何时插入产品 我从default_products_code获取代码，并从default_category获取符号 并为产品A编写这样的代码，例如，代码为SMT0001 然后我将default_products_code中的代码更新为2，因为1 在product_code中分配.这就是我当前的系统.但是现在的问题 到了我的数据库中有数百种产品，我必须更新 我按照上述条件新添加了列.
Whenever a category is created i make an entry in default_products_code and provide code = 1 as default value. Now when ever a product is inserted i get the code from default_products_code and symbol from default_category and make a code like this for example for product A the code is SMT0001 and than i update the code in default_products_code to 2 because 1 has been assigned in the product_code. This is what my current system is. But now the problem comes that there are hundrad of products in my database and i have to update the newly added column with the above criteria i explained.
这是我尝试使用自动递增的查询
This is the query i have tried with auto increment
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，但我无法继续.
Another thing i need to update all products within the table. How can i do this in a single query.I know i need to remove WHERE subcategory_id = 1 in this case but i am unable to proceed.
推荐答案
在进行一些修改之后，我发现了如何做.
Well after some modifications i have found how to do it.
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;
我已经完成了代码创建，并且它完全可以按照我的需要工作.我仍然想用 条件已删除的地方，以便我可以更新所有产品.
I have taken out code creation and it is working exactly as i need. I still want to update it with the where condition removed so i can update all products.
这篇关于使用MySQL使用LEFT JOIN，变量和自动增量更新查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
