CODEWITHSUNDEEP
mysqlinsert-into
victor zubiaga
victor zubiaga

Reputation: 97

MySQL Insert from another table with variable increment

I have two tables in mysql. One called registros_temporal with all columns of registros except one column called id.

I need to insert all rows from registros_temporal into registros and the column id incrementing with a factor.

Structures:

registros:
id, fecha, codigos... (50 columns)

registros_temporal
fecha, codigos... (49 columns)

My query is 

SET @mes = 01;
SET @anho = 2016;

SET @id = (@anho-2000)*1000000000 + @mes*10000000;
SELECT @id;

INSERT INTO registros
(
SELECT (@id := @id + 1) AS id, ll.* FROM registros_temporal ll 
ORDER BY fecha ASC
)
;

SELECT (@id := @id + 1) AS id, ll.* FROM registros_temporal ll 
ORDER BY fecha ASC;

When the data is load i have an error in id column. All are 2147483647 when i expect 16010000001, 16010000002, 16010000003, 16010000004, ...

Id column is an integer field

Upvotes: 1

Views: 373

Answers (1)

algor
algor

Reputation: 414

2147483647 is the largest int value in MySQL for signed int data type. Initial value 16010000000 is greater than 2147483647. Change your column type to bigint

Upvotes: 4

Related Questions