12

Here is the format of the table:

indexer group name id
1       abc   a
2       abc   b
3       xyz   c
4       abc   e
5       xyz   d

Now i want it to be like,

indexer group name id
1       abc   a    1
2       abc   b    2
3       xyz   c    1
4       abc   e    3
5       xyz   d    2

"id" should auto increment according to "group"

Heider Kumar
  • 135
  • 1
  • 1
  • 7
  • Not possible in MySQL. You could write a stored procedure to do the `INSERT` that calculated the next id - but you'd probably be better off generating the sequence externally; in whatever program you use to insert the data. – Boris the Spider Aug 03 '16 at 07:51
  • It doesn't make much sense anyway, what are you trying to do exactly ? – polku Aug 03 '16 at 07:52
  • Oh!! Where it will be possible? – Heider Kumar Aug 03 '16 at 07:53
  • 1
    Actually i have a large database, which is impossible to do that by manually. So i thought if there is any other way to do so. – Heider Kumar Aug 03 '16 at 07:54
  • So you want to add a column into your table? – Blank Aug 03 '16 at 07:58
  • I think, i can do this via mysql query. First i will query according to the "group" via serial i++ and update the table "id" accordingly – Heider Kumar Aug 03 '16 at 07:59
  • @JPG i already had the column "id" in the table which is null for now. I want to add serial numbers to the column "id" according to the group. – Heider Kumar Aug 03 '16 at 08:01

2 Answers2

7

Try this:

update yourtable t1
join (
    select
          tt.indexer, @rowno := if(@grp = `group`, @rowno + 1, 1) as id, @grp := `group`
    from (select * from yourtable order by `group`, indexer) tt
    cross join (select @rowno := 0, @grp := null) t
) t2
on t1.indexer = t2.indexer
set t1.id = t2.id

Demo Here

Edited:

If you want to insert a new row, you have to do it like this:

insert into yourtable
select '$indexer', '$group', '$name', coalesce(max(id), 0) + 1
from yourtable
where name = '$name'
Blank
  • 12,308
  • 1
  • 14
  • 32
  • Later when i insert a new row, will "id" auto insert the number or i have to run the code again? – Heider Kumar Aug 03 '16 at 09:00
  • @HeiderKumar Check the ***Edited*** part. – Blank Aug 03 '16 at 09:06
  • 1
    @HeiderKumar Please note, that the way to insert new rows won't work. It won't insert anything, when the select doesn't find an entry in the table. Also be aware, that this solution will have horrible performance when you have much data. It will always do a full table scan. – fancyPants Aug 03 '16 at 10:24
  • Another drawback is, that when you have heavy traffic on your database, it may happen, that multiple entries end up with the same id. – fancyPants Aug 03 '16 at 10:39
  • 1
    @fancyPants All you've said is absolutely right, but here just as a kind of solution. Sometimes we give an answer, then he find it can solve his problem, it's there. Of course, I respect your effort on your answer and learned a lot from it. – Blank Aug 03 '16 at 10:46
3

If you want to use a built in function, you have to use a MyISAM table. Be aware though, that those do not support transactions and use table level locks and so on. You might want to read about that. If you're fine with it, here's how. If you want to use InnoDB or other engines, you will have to write your own solution, i.e. a stored procedure.

Quote from the manual:

For MyISAM tables, you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

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)
) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

Which returns:

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

  • In this case (when the AUTO_INCREMENT column is part of a multiple-column index), AUTO_INCREMENT values are reused if you delete the row with the biggest AUTO_INCREMENT value in any group. This happens even for MyISAM tables, for which AUTO_INCREMENT values normally are not reused.

  • If the AUTO_INCREMENT column is part of multiple indexes, MySQL generates sequence values using the index that begins with the AUTO_INCREMENT column, if there is one. For example, if the animals table contained indexes PRIMARY KEY (grp, id) and INDEX (id), MySQL would ignore the PRIMARY KEY for generating sequence values. As a result, the table would contain a single sequence, not a sequence per grp value.

fancyPants
  • 50,732
  • 33
  • 89
  • 96