mysql自增量查询,使用MySQL使用LEFT JOIN，变量和自动增量更新查询

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.