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, Feb 22 '13 at 21:08
are you looking for string modification in your queries, to set the product_code according to your id? Something like http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_format — EGOrecords, Feb 22 '13 at 21:13

score 2 · Answer 1 · answered Feb 22 '13 at 21:13

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 '13 at 21:13

echo_Me

37,078
5
58
78

yes but why in my query @var := @var+1 does not have eny effect? – Muhammad Raheel Feb 22 '13 at 21:16
u dont need this `@var := @var+1` just remove it – echo_Me Feb 22 '13 at 21:17
u dont need that variable `$var` at all it will follows the `id` – echo_Me Feb 22 '13 at 21:18
`LPAD(id, 5, 0)` look that , it will follow the id automatically so u dont need to put variable – echo_Me Feb 22 '13 at 21:19
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 Feb 22 '13 at 21:21
then make variable for the 5 to adjust the length. if length of id is 2 then this new_variable will not be 5 it will be 4 just example – echo_Me Feb 22 '13 at 21:23
create vraiable for the length 5 – echo_Me Feb 22 '13 at 21:24
other option is to make as you done and make a loop like that `SET @var = 1; insert_loop: LOOP` ...........`END LOOP subcat_loop;` – echo_Me Feb 22 '13 at 21:30
look here how it explained the loop for your variable http://stackoverflow.com/questions/4107803/how-to-use-nested-loops-and-varaibles-in-mysql-statement – echo_Me Feb 22 '13 at 21:32
but i had to do it manually but i am still interested to see how i can do it with only a single query – Muhammad Raheel Feb 23 '13 at 19:48
where is or from where it comes this `part_code` ? have you column of it? – echo_Me Feb 23 '13 at 21:29

score 1 · Accepted Answer · answered Feb 22 '13 at 21:37

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.

@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 — Muhammad Raheel, Feb 23 '13 at 08:05

Update Query with LEFT JOIN , Variable and Autoincrement Using MySQL

2 Answers2

Linked