How to Resolve \"Column count doesn\'t match value count\" Error When Inserting Data in MySQL?

Inserting Data from One Table to Another in MySQL

When attempting to insert data from one table into another using a query like:

INSERT INTO mt_magazine_subscription ( 
      magazine_subscription_id, 
      subscription_name, 
      magazine_id, 
      status ) 
  VALUES ( 
      (SELECT magazine_subscription_id, 
              subscription_name, 
              magazine_id 
       FROM tbl_magazine_subscription 
       ORDER BY magazine_subscription_id ASC), '1')

you may encounter the error message:

#1136 - Column count doesn't match value count at row 1

This is because the number of columns specified in the INSERT statement does not match the number of values provided in the subquery.

Solution

To resolve this issue, use the INSERT...SELECT syntax, as follows:

INSERT INTO mt_magazine_subscription ( 
      magazine_subscription_id, 
      subscription_name, 
      magazine_id, 
      status ) 
SELECT magazine_subscription_id, 
       subscription_name, 
       magazine_id, 
       '1'
FROM tbl_magazine_subscription
ORDER BY magazine_subscription_id ASC 

In this corrected query, the '1' value for the status column is quoted directly in the SELECT part. By using the INSERT...SELECT syntax, you specify the columns to be inserted into and select the values to be inserted from another table or subquery, ensuring that the number of columns and values matches.

The above is the detailed content of How to Resolve \"Column count doesn\'t match value count\" Error When Inserting Data in MySQL?. For more information, please follow other related articles on the PHP Chinese website!