CODEWITHSUNDEEP
mysql
Patrick
Patrick

Reputation: 53

mySql insert needs all columns noted

I recently started hosting my own site and mySQL db. Everything works fine, but whenever I do an insert or update to any of tables, it errors out if I do not call out each and every column. Is there a setting for the table or the DB that controls this? I never had to do this with my previous DB host.

Thanks.

Upvotes: 0

Views: 113

Answers (2)

Barmar
Barmar

Reputation: 781096

There are several ways to write an INSERT query.

  1. INSERT INTO tablename (col1, col2, ...) VALUES (val1, val2, ...)

  2. INSERT INTO tablename (col1, col2, ...) SELECT ...

  3. INSERT INTO tablename VALUES (val1, val2, ...)

  4. INSERT INTO tablename SELECT ...

In the first two methods, you don't have to list all the columns, since you specify the columns to be filled in explicitly. The columns that aren't in the (col1, col2, ...) list will get their default values. The VALUES list or the SELECT query must return as many columns as you specified in the list.

In methods 3 and 4, MySQL requires the VALUES list or the SELECT query to return as many columns as the table contains, and they must be in the same order as the table definition. I can't find any setting that disables the column count check in these methods.

In methods 1 and 3, you can put the keyword DEFAULT in place of any of the column values to insert its default value.

If your server is in STRICT mode, you also have to explicitly set any columns that do not have a DEFAULT option in the schema. If it's not in strict mode, automatic defaults will be used.

Upvotes: 1

O. Jones
O. Jones

Reputation: 108686

It probably doesn't fail if you UPDATE the values of some, but not all, columns in particular rows.

When you create your table, you need to set a default value for each column which you may choose to omit in your INSERT statements.

Upvotes: 1

Related Questions