MySQL insert newbie question.

Question

Is this possible or do I have to list all the columns?

INSERT INTO table_name (column1, column3, column2,...) VALUES (value1, value2, value3,...)

Do I have to list all the columns in order and have values for each one?

Answer 1

You only have to put the columns you plan on inserting. The only order that needs to match up is the column name and value.

IE: 3 Columns: col1, col2,col3

INSERT INTO TABLE (col1, col2) VALUES(col1value, col2value)

INSERT INTO TABLE (col2, col3) VALUES(col2value, col3value)

INSERT INTO TABLE (col3, col2) VALUES(col3value, col2value)

Answer 2

For your INSERT statement it should be like this:

INSERT INTO table_name (column1, column3, column2,...) VALUES (value1, value3, value2,...)

Since you moved column3, value3 should "match up" with it

Answer 3

You can put your query columns in any order, as long as you specify that order like you are doing above. The actual values you insert as part of the VALUE clause must match the INSERT INTO (x,y,z) part of your query.

Any columns that you do not specify will have a default value inserted. The default value is determined by the DEFAULT value set when the column was created.

Your INSERT can fail if a column has a NOT NULL specification on it and no DEFAULT value, and you do not provide one in the query.

Answer 4

Your column names must correspond to the values that you are inserting. For instance, in your above query, if column1 is a varchar, column3 an int, etc, the values must correspond and be in the exact order for your query to execute successfully.