CODEWITHSUNDEEP
mysql
glen206
glen206

Reputation: 21

How to backfill an auto increment field in MySQL

I have a MySQL Database 5.6.32 connected to SuiteCRM. I am using a plugin that allows for the creation of an auto increment field without any coding.

The challenge I'm having is that I have created this auto increment field after records with data are already in our system. I need to update all previous entries with the auto increment values.

When I create a new record the auto increment field works fine, but I need the unique number for all records as this is being used as a Unique Identifier and the default ID in the system is too long for us to use.

The type of auto increment field it created in the MySQL database is as follows:

#   Name                Type        Collation               Null    Default
10  customer_number_c   varchar(80) utf8_general_ci         Yes     NULL

This is what I have tried so far to try and populate the field:

 UPDATE `suitecrm`.`accounts_cstm` SET `customer_number_c` = auto_increment

The result is:

ERROR #1054 - Unknown column 'AUTO_INCNREMENTAL' in 'Field list'

The field already has a default value of NULL in it as well.

Upvotes: 2

Views: 2188

Answers (2)

Asaph
Asaph

Reputation: 162831

MySQL will retroactively populate existing rows for you if you add an auto_increment primary key. I just validated this with the following test code:

mysql> create table mytable (name varchar(32)) engine=innodb;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into mytable (name) values ('miles'), ('trane'), ('monk');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from mytable;
+-------+
| name  |
+-------+
| miles |
| trane |
| monk  |
+-------+
3 rows in set (0.00 sec)

mysql> alter table mytable add column id int unsigned primary key auto_increment first;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from mytable;
+----+-------+
| id | name  |
+----+-------+
|  1 | miles |
|  2 | trane |
|  3 | monk  |
+----+-------+
3 rows in set (0.00 sec)

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562701

MySQL's builtin auto-increment feature only works with columns of integer data types. Yours is varchar(80). I'm not sure why you did that, but I assume it was deliberate.

You could use a session variable to populate the customer number. As you assign values, it will implicitly cast the integer value of the session variable to the string representation.

SET @num := 0;

UPDATE suitecrm.accounts_cstm 
SET customer_number_c = (@num := @num + 1) 
ORDER BY ...;

You would have to specify some ORDER BY clause to make sure the increasing values get assigned in the order you want them to be.

But you still won't be able to use AUTO_INCREMENT on the customer_number_c column. So your app must generate new customer number values before inserting new rows to this table.

Upvotes: 3

Related Questions