How to Correctly Insert Data into a MySQL Table Using SELECT and Default Values?

Insert into MySQL Table with Select and Default Values: Resolving Errors

In MySQL, inserting data into a table using select statements and default values can sometimes lead to errors. Let's address the specific error messages encountered in the provided queries.

Error 1: MySQL Error

The query INSERT INTO def (catid, title, page, publish) (SELECT catid, title from abc),'page','yes') results in a MySQL error because it attempts to insert multiple sets of data without specifying the column names for the default values.

Error 2: Column Count Mismatch

The query INSERT INTO def (catid, title, page, publish) VALUES((SELECT catid, title from abc),'page','yes')) causes a column count mismatch error because it specifies more values in the SELECT subquery than in the VALUES clause.

Solution

To resolve these errors, the correct way to insert into a table with SELECT statements and default values is as follows:

INSERT INTO def (catid, title, page, publish) 
SELECT catid, title, 'page', 'yes' 
FROM `abc`

This query explicitly specifies the default values for page ('page') and publish ('yes') in the SELECT statement. By matching the column names in the INSERT INTO clause with the columns selected in the SELECT subquery, the column count error is also avoided.

This updated query should successfully insert the data from the abc table into the def table, along with the default values for page and publish.

The above is the detailed content of How to Correctly Insert Data into a MySQL Table Using SELECT and Default Values?. For more information, please follow other related articles on the PHP Chinese website!