Keboola WIKI‎ > ‎Transformations‎ > ‎MySQL‎ > ‎

AUTO_INCREMENT Grouped By Column

by martin.matejka@keboola.com


This query creates table which has AUTO_INCREMENT grouped by specific column. You can’t have Primary Key set to a different column(s) then AUTO_INCREMENT field in InnoDB engine, so you need to create table in MYISAM engine.


Example:


Before

ID

Product ID

Col1

A

112

Value

B

112

Value

C

112

Value

D

112

Value

E

250

Value

F

250

Value


After

ID

Product ID

Col1

Auto_Increment_field

A

112

Value

1

B

112

Value

2

C

112

Value

3

D

112

Value

4

E

250

Value

1

F

250

Value

2


Query:


CREATE TABLE `table_increment` (

  `Auto_Increment_field`    INT AUTO_INCREMENT NOT NULL,

  `Product ID`    VARCHAR(255) NOT NULL,

  `ID`            VARCHAR(255) NOT NULL,

  PRIMARY KEY(`productId`,`Auto_Increment_field`)

) ENGINE=MYISAM DEFAULT CHARSET=utf8;


INSERT INTO `table_increment` (`Product ID`,`ID`)

SELECT `Product ID`,`ID`

FROM `table`

;


Comments