I am stuck on a situation. Here is the table structures.
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
Now here is the detail
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;
It produces this result
id | subcategory_id | product_name | product_code
---------------------------------------------------
1 | 1 | A | SMT00001
2 | 1 | B | SMT00001
5 | 1 | E | SMT00001
8 | 1 | H | SMT00001
While i need this
id | subcategory_id | product_name | product_code
---------------------------------------------------
1 | 1 | A | SMT00001
2 | 1 | B | SMT00002
5 | 1 | E | SMT00003
8 | 1 | H | SMT00004
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.