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

227

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 followinginsert 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)
)
932

Answer

Solution:

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.

454

Answer

Solution:

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

People are also looking for solutions to the problem: php MySQL CRUD: Am I deleting safely?

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.