How to Correctly Insert Data from One Table to Another in MySQL?

Inserting Data from One Table to Another in MySQL

Inserting data from one table to another is a common operation in database management. However, it can sometimes lead to errors if the syntax is not correct.

Consider the following query:

<code class="sql">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')</code>

This query aims to insert data from the tbl_magazine_subscription table into the mt_magazine_subscription table. However, it results in the error:

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

To resolve this error, we can use the INSERT...SELECT syntax. The updated query is:

<code class="sql">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</code>

In this revised query, we directly specify the value of the status column as '1' within the SELECT statement, ensuring that the number of values matches the number of columns in the target table. This modification allows the data insertion to proceed successfully.

The above is the detailed content of How to Correctly Insert Data from One Table to Another in MySQL?. For more information, please follow other related articles on the PHP Chinese website!