Reputation: 97
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
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