CODEWITHSUNDEEP
sqlmysqlmysql-error-1136
tsgrasser
tsgrasser

Reputation: 712

MySQL: Multiple Inserts for a single column

I'm looking for a way to do multiple row inserts when I'm only inserting data for a single column.

Here is the example table:

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | tinyint(4)  | NO   | PRI | NULL    | auto_increment | 
| name  | varchar(40) | NO   | UNI | NULL    |                | 
+-------+-------------+------+-----+---------+----------------+

I want to be able to insert something like ('admin', 'author', 'mod', 'user', 'guest') into the name column for each row.

The MySQL documentation shows that multiple inserts should be in the format:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

However my statement ends up looking like this:

INSERT INTO User_Role(name) VALUES ('admin','author','mod','user','guest');

And I get the following:
ERROR 1136 (21S01): Column count doesn't match value count at row 1

Meaning that it thinks I'm trying to do a single row insert.

I'm not sure if I'm just missing something simple here, but I don't see anything in particular in the MySQL docs for this use case.

Upvotes: 11

Views: 54340

Answers (2)

Pooja Khatri
Pooja Khatri

Reputation: 580

I will advise you Don't put multiple values in a column. make a new table:

     INSERT INTO table_name (id, name) VALUES (1, 'name1'), (1, 'name2'), (1, 'name3'), (1, 'name4');

Upvotes: 1

Scott M.
Scott M.

Reputation: 7347

your syntax is a bit off. put parentheses around each data "set" (meaning a single value in this case) that you are trying to insert.

INSERT INTO User_Roll(name) VALUES ('admin'), ('author'), ('mod'), ('user'), ('guest');

Upvotes: 28

Related Questions