sqlmysql-error-1292

SQL (String+Int) Variables Insert Loop


iam trying to add multi records into a table using that code

BEGIN
    SET @i:=10000;
    WHILE @i <= 10099 DO
        INSERT INTO wp_genpro_products (serial, guarantee) VALUES ('HMAX'+@i, 0);
        SET @i = @i + 1;
    END WHILE;
END

it works fine with only numbers without the add of 'HMAX'+ but when tried to insert letters with the numbers it gives me #1292 error - truncated incorrect double value

CREATE TABLE `wp_genpro_products` (
  `id` mediumint(9) NOT NULL,
  `serial` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `guarantee` mediumint(255) DEFAULT NULL,
  `reg_date` date DEFAULT NULL
)

Solution

  • solved by using CONCAT()

    BEGIN
        SET @i:=10000;
        WHILE @i <= 10099 DO
            INSERT INTO wp_genpro_products (serial, guarantee) VALUES (CONCAT('HMAX',@i), 0);
            SET @i = @i + 1;
        END WHILE;
    END