MySQL: Manually increment a varchar for one insert statement

Question

I am converting data from one database to another. The target db has a table called provider with primary key provider_no varchar(6).

I'm writing an insert to copy from source table to target table, and need an incremented key for provider_no. Is there a function to return even the iterations for one insert statement?

There are a lot more columns, but the basic problem i'm trying to solve is:

INSERT INTO `target`.`provider`
  (`provider_no`,
  `lastUpdateDate`)
SELECT 
  '', --incremented value
  now()
from `source`.`provider`;

Auto Increment only works for int values, but i'm not at liberty here to change the data type.

Also, the source table doesn't have a usable primary key value that I can use for this copy.

Can you provide a sample data as to what the value looks like in your auto incremented varchar column? — Rahul Tripathi, Apr 16 '15 at 04:58
http://stackoverflow.com/questions/14966824/increment-a-varchar-in-sql — Techy, Apr 16 '15 at 04:59
http://stackoverflow.com/questions/4699591/how-to-autoincrement-a-varchar — Techy, Apr 16 '15 at 05:00
The database defines it as a varchar, however the client application programatically requires it to be an integer for some reason. These are all valid IDs in the test schema I have: -1, 99998, 1, -1015 — Ian Pert, Apr 16 '15 at 05:10

pala_ · Accepted Answer · 2015-04-16T05:46:35.340

4

To increment the varchar, first cast it to a number (either signed, or unsigned) like so:

INSERT INTO `target`.`provider`
  (`provider_no`,
  `lastUpdateDate`)
SELECT 
  cast(the_varchar_field as signed) + 1, --incremented value
  now()
from `source`.`provider`;

Example:

mysql> select cast("001" as unsigned) + 1;
+-----------------------------+
| cast("001" as unsigned) + 1 |
+-----------------------------+
|                           2 |
+-----------------------------+

Sorry, i thought you wanted to increment the varchar field from the source table.

To 'emulate' an auto increment field as you want to do, we can do it with variables like this:

insert into provider 
  select @cnt := @cnt +1, now()
    from sourceprovider, (select @cnt := 0) q;

And here's a little demo: http://sqlfiddle.com/#!9/09fd4/1

edited Apr 16 '15 at 05:46

answered Apr 16 '15 at 05:12

pala_

8,901
1
15
32

Ah, that's a neat one. However, in the source shema the unique key is (oh, good lord i know this is terrible) a two-character varchar and the target is a varchar that is programmatically required to be an int. A very unique problem I never had before. Posted my solution below. – Ian Pert Apr 16 '15 at 05:20
oh, you just wanted to insert a NEW auto incrementing value into the provider table? – pala_ Apr 16 '15 at 05:42

score 0 · Answer 2 · answered Apr 16 '15 at 05:17

Here's a solution I just implemented. There's a unique column called did on the source table, which I can use indirectly. There's probably a more elegant way to do it, but:

create table temp_key (
key_id int auto_increment primary key,
did varchar(4));

insert into temp_key (did) select did from source.provider;

INSERT INTO target.provider
  (provider_no,
  lastUpdateDate)
SELECT
  k.key_id,
  now()
FROM source.provider d
JOIN temp_key k on d.did = k.did;


drop table temp_key;

sorry i completely misinterpreted what you were asking. i amended my answer to accomplish the above, without a temporary table. — pala_, Apr 16 '15 at 05:47

MySQL: Manually increment a varchar for one insert statement

2 Answers2