Error Code: 1136. Column count doesn’t match value count at row 1

Posted on

Question :

I’m struggling here. This is my code

CREATE DEFINER=`****`@*****` PROCEDURE `setCountry`(
    IN `countryname` VARCHAR(100) CHARSET utf8mb4,
    IN `nationality` VARCHAR(100) CHARSET utf8mb4,
    IN `countryPrefix` INT(3)
)
    MODIFIES SQL DATA
INSERT INTO country(
    countryName,
    nationality,
    countryPrefix
)
SELECT
    @countryname
FROM
    (
    SELECT
        @countryname AS countryName,
        @nationality AS nationality,
        @countryprefix AS countryPrefix
) AS tmp
WHERE NOT EXISTS
    (
    SELECT
        countryName
    FROM
        country
    WHERE
        countryName = @countryname
)
LIMIT 1

And i’m trying to execute code with this sentence

call ccc.setCountry('Togo', 'Togolese', 228);

The ERROR it displays is this :

Error Code: 1136. Column count doesn’t match value count at row 1

Answer :

You beed to select 3 columns for4 the insert.

and @countryname and countryName are different variables

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `setCountry`(
    IN `countryname` VARCHAR(100) CHARSET utf8mb4,
    IN `nationality` VARCHAR(100) CHARSET utf8mb4,
    IN `countryPrefix` INT(3)
)
    MODIFIES SQL DATA
BEGIN
      SET @countryname := countryName;
      SET  @nationality := nationality;
      SET @countryprefix := countryPrefix;

    INSERT INTO country(
        countryName,
        nationality,
        countryPrefix
    )
    SELECT    
        countryname,
        nationality,
        countryPrefix
    FROM
        (
        SELECT
            @countryname AS countryName,
            @nationality AS nationality,
            @countryprefix AS countryPrefix
    ) AS tmp
    WHERE NOT EXISTS
        (
        SELECT
            countryName
        FROM
            country
        WHERE
            countryName = @countryname
    )
    LIMIT 1;
END$$

DELIMITER ;

Change from

SELECT
@countryname
FROM
(
SELECT
@countryname AS countryName,
@nationality AS nationality,
@countryprefix AS countryPrefix

to

SELECT
    @countryname AS countryName,
    @nationality AS nationality,
    @countryprefix AS countryPrefix

Leave a Reply

Your email address will not be published. Required fields are marked *