0

I have a table like this 

+------------------+------------------+------+-----+-------------------+-----------------------------+
| Field            | Type             | Null | Key | Default           | Extra                       |
+------------------+------------------+------+-----+-------------------+-----------------------------+
| id               | int(10) unsigned | NO   | PRI | NULL              | auto_increment              |
| user_id          | int(10) unsigned | NO   |     | NULL              |                             |
| arena_ids        | json             | NO   |     | NULL              |                             |
| organization_id  | int(10) unsigned | NO   | MUL | NULL              |                             |
| type             | tinyint(4)       | NO   |     | NULL              |                             |
| registration_fee | double           | YES  |     | NULL              |                             |
| tax_group_id     | int(10) unsigned | YES  | MUL | NULL              |                             |
| from_date        | date             | YES  |     | NULL              |                             |
| to_date          | date             | YES  |     | NULL              |                             |
| status           | varchar(10)      | YES  |     | NULL              |                             |
| comments         | varchar(100)     | YES  |     | NULL              |                             |
| created_at       | datetime         | NO   |     | CURRENT_TIMESTAMP |                             |
| updated_at       | datetime         | YES  |     | NULL              | on update CURRENT_TIMESTAMP |
| deleted_at       | datetime         | YES  |     | NULL              |                             |
+------------------+------------------+------+-----+-------------------+-----------------------------+

Now in this table I have to add one more column serial number and that serial number will auto_increment but in my case it is something different I have to increment serial_number until organization_id changes and if organization_id changes so now again serial_number starts from 1 again. 

`serial_number`   `organization_id`
     1                   1
     2                   1
     1                   2
     3                   1
     2                   2

while i was reading the documentation I found some example like this but I here I have to update not create 

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) 

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

How to achieve this thing in update query .

Vikas
  • 975
  • 1
  • 10
  • 34
  • The update is only part of the answer, because you will still have new data coming in. – Tim Biegeleisen Apr 19 '18 at 08:38
  • Yes I want to add new column also `serial_number` – Vikas Apr 19 '18 at 08:39
  • Basically a duplicate of: https://stackoverflow.com/questions/677542/auto-increment-by-group ... I won't close you out, but this answer is probably the best you can do in MySQL. – Tim Biegeleisen Apr 19 '18 at 08:40
  • @TimBiegeleisen I have actually mentioned this in my post I have read this but I want to update the table and want to add the new column and make it like this because i have already defined my database earlier – Vikas Apr 19 '18 at 08:44
  • @TimBiegeleisen Now I want to change the table as because the project is live and I can not drop the table – Vikas Apr 19 '18 at 08:45

0 Answers0