Update Query with LEFT JOIN , Variable and Autoincrement Using MySQL

Question

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.

you should back to my query solution in your previous question :) — echo_Me
– echo_Me, Commented Feb 22, 2013 at 21:08
are you looking for string modification in your queries, to set the product_code according to your id? Something like dev.mysql.com/doc/refman/5.1/en/… — EGOrecords
– EGOrecords, Commented Feb 22, 2013 at 21:13

echo_Me · Accepted Answer · 2013-02-22 21:13:33Z

2

your problem is with LPAD

LPAD is defined like that

   LPAD(str, len, padstr)

in your query u should use like that

 LPAD(id, 5, 0)

in your query LPAD(@var,5,'0') it will return always 00001 as @var is 1

answered Feb 22, 2013 at 21:13

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Muhammad Raheel Over a year ago

yes but why in my query @var := @var+1 does not have eny effect?

echo_Me Over a year ago

u dont need this @var := @var+1 just remove it

echo_Me Over a year ago

u dont need that variable $var at all it will follows the id

echo_Me Over a year ago

LPAD(id, 5, 0) look that , it will follow the id automatically so u dont need to put variable

Muhammad Raheel Over a year ago

i have provided id as sample data. products may be in any order some ids missing in the middle what will i do than? for example 1 ,2 3, 10 , 16, 100. In this case i can't use id.

|

Muhammad Raheel · Accepted Answer · 2013-02-22 21:37:37Z

1

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.

answered Feb 22, 2013 at 21:37

Muhammad Raheel

19.9k7 gold badges69 silver badges106 bronze badges

2 Comments

Muhammad Raheel Over a year ago

@BillKarwin can you please help me with this

Muhammad Raheel Over a year ago

@JW can you help me with this. I want to update all records for which i know i will have to reset variable to 0 each time when a new group is selected

Collectives™ on Stack Overflow

Update Query with LEFT JOIN , Variable and Autoincrement Using MySQL

2 Answers 2

11 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related