0

I have a table code with the following table structure. id is auto incrementing id, the code is a random unique 15 digit code and customer_id is the customer's id. The table has close to 0.13 million rows

 id      code    customer_id 
 1     123...4      1            
 2     124...5      1            
 3     128...3      1            
 4     234...1      2            
 5     678...3      2            
 6     567...8      1

I have added a column serial_number in table and I want to add a serial_number which will increment for each value of respective customer like below based on the customer_id for existing values. How do I do this in mysql?

 id    code       customer_id  serial_number
 1     123...4      1            1
 2     124...5      1            2
 3     128...3      1            3
 4     234...1      2            1
 5     678...3      2            2
 6     567...8      1            4

I tried this approach but my mysql connection is getting timed out:

MySQL: Add sequence column based on another field

CREATE temporary table serial_number ( id bigint, serial_number int);
INSERT INTO serial_number ( id, serial_number )
    SELECT id, 
      (SELECT count(*) + 1 FROM code c 
      WHERE c.id < code.id AND c.customer_id = code.customer_id) as    serial_number 
    FROM code;

UPDATE test INNER join serial_number ON code.id = serial_number.id SET   code.serial_number = serial_number.serial_number;

Error Code: 2013. Lost connection to MySQL server during query

FBP
  • 345
  • 3
  • 15
  • It would be useful if we could see what exact query you used, whether you created a trigger / stored procedure / event or if you're doing this from within your application code. That's why it's always good to post your code, not concepts. – Mjh Jun 09 '17 at 08:34
  • i had already put it in my question . here it is again : https://stackoverflow.com/questions/1600294/mysql-add-sequence-column-based-on-another-field?rq=1 – FBP Jun 09 '17 at 08:37
  • Don't get me wrong, I posted what I did for a reason. It might seem silly to you that I'm asking for that information, but giving the link that you gave without your actual table structure and **how** you are running the query will yield you almost no help. It's your question and your problem, i tried to help, no one can force you to provide all the info if you don't want to. Good luck with this! – Mjh Jun 09 '17 at 08:41
  • @Mjh Thanks. But my table structure is as above. I have added the code I used based on the link above. – FBP Jun 09 '17 at 08:44

0 Answers0