0

My table has a column, "Company_Name", like company_A, company_B, etc. I want another column, "Company_ID", where each company will have an integer ID starting with 1 and auto-incremented whenever company name changes.

Note that the Company_Name is not the primary key in my table. So, there will be multiple entires of a company name (and I cannot do a simple auto_increment for the company ID).

For example:

company_A
company_A
company_A
company_B
company_B

should yield

1
1
1
2
2
Joyel
  • 53
  • 1
  • 1
  • 6
  • *"with names grouped together*" <- what does that even mean? – Phil Sep 03 '17 at 23:38
  • 2
    You want this stored permanently somewhere? Or what? Why not just make a company names table with a primary key `id` and a unique on name, then just `INSERT IGNORE INTO ...` to get IDs back. – tadman Sep 03 '17 at 23:40
  • The table has several other entries too. So, I need to add one column indicating the column IDs. It will not be a primary ID, as there will be multiple entries of one company. It's a temporal data, so the time is unique in the table. – Joyel Sep 03 '17 at 23:45
  • You could use an on update before trigger. Test to see if the company_name column is being changed, then increment the company_id column. – Sloan Thrasher Sep 03 '17 at 23:50
  • The table is already there with all the company names. Company_name column is never updated. – Joyel Sep 03 '17 at 23:52

0 Answers0