CODEWITHSUNDEEP
phpmysql
Average Joe
Average Joe

Reputation: 4601

what's wrong with this SQL statement causing column count doesn't match value count at row 1?

Mysql table (migration_terms) fields are as follows

oldterm   count    newterm  seed

I used the following create table statment. 

CREATE TABLE `migration_terms` 
    ( 
      `oldterm` varchar(255) DEFAULT NULL, 
      `count` smallint(6) DEFAULT '0', 
      `newterm` varchar(255) DEFAULT NULL, 
      `seed` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`seed`) 
    )   ENGINE=InnoDB DEFAULT CHARSET=utf8

And It works, no problems there.

but then when I used the following insert into statement to populate it;

"INSERT INTO migration_terms 
     SELECT looseterm as oldterm, 
            COUNT(seed) AS count 
            FROM looseterms 
            GROUP BY looseterm 
            ORDER BY count DESC "

I get this error; 

 Column count doesn't match value count at row 1

I cannot figure out why?

If you need the table structure of the looseterms table, it was created by the following create table statement.

CREATE TABLE looseterms
(
     `seed` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
     `looseterm` varchar(255)
)

Upvotes: 1

Views: 134

Answers (2)

Conrad Frix
Conrad Frix

Reputation: 52655

You need to specify the columns if your select statement has fewer columns than the table

"INSERT INTO migration_terms 
        (oldterm, 
         count) 
SELECT looseterm   AS oldterm, 
       Count(seed) AS count 
FROM   looseterms 
GROUP  BY looseterm 
ORDER  BY count DESC  "

From MySql docs on Insert Syntax

If you do not specify a list of column names for INSERT ... VALUES or INSERT ... SELECT, values for every column in the table must be provided by the VALUES list or the SELECT statement. If you do not know the order of the columns in the table, use DESCRIBE tbl_name to find out.

Upvotes: 8

Bryan
Bryan

Reputation: 6752

Your insert is adding 2 columns of data, whereas your table's definition has 4 columns

Upvotes: 4

Related Questions