CODEWITHSUNDEEP
mysqlshell
Victor Escalona
Victor Escalona

Reputation: 615

I can't insert values to mysql table using the shell

I've been studying mysql in SHELL and I'm trying to insert values to a table using INSERT INTO....

ERROR 1136 (21S01): Column count doesn't match value count at row 1

The table I'm using:

enter image description here

And this is how I'm trying to insert the values:

INSERT INTO clients VALUES 
('Maria Dolores Gomez','Maria [email protected]','1971-06-06','F',1,'2018-04-09 16:51:30'),
('Adrian Fernandez','[email protected]','1970-04-09','M',1,'2018-04-09 16:51:30'),
('Maria Luisa Marin','Maria [email protected]','1957-07-30','F',1,'2018-04-09 16:51:30'),
('Pedro Sanchez','[email protected]','1992-01-31','M',1,'2018-04-09 16:51:30');

Upvotes: 0

Views: 82

Answers (4)

Ed Bangga
Ed Bangga

Reputation: 13026

You specified the update_at column with default value of current_timestamp, add default on your insert script for both auto increment field and update_at columns.

insert into clients values
(default, 'Maria Dolores Gomez','Maria [email protected]','1971-06-06','F',1,'2018-04-09 16:51:30', default),
(default, 'Adrian Fernandez','[email protected]','1970-04-09','M',1,'2018-04-09 16:51:30', default),
(default, 'Maria Luisa Marin','Maria [email protected]','1957-07-30','F',1,'2018-04-09 16:51:30', default),
(default, 'Pedro Sanchez','[email protected]','1992-01-31','M',1,'2018-04-09 16:51:30', default);

See dbfiddle to test.

Upvotes: 2

Akina
Akina

Reputation: 42834

You specify 6 values for each record in VALUES clause dataset whereas table structure contains 8 fields.

You must specify 6 fields which value is specified in your query for:

INSERT INTO clients (name, email, birthdate, gender, active, created_at)
VALUES 
('Maria Dolores Gomez','Maria [email protected]','1971-06-06','F',1,'2018-04-09 16:51:30'),
('Adrian Fernandez','[email protected]','1970-04-09','M',1,'2018-04-09 16:51:30'),
('Maria Luisa Marin','Maria [email protected]','1957-07-30','F',1,'2018-04-09 16:51:30'),
('Pedro Sanchez','[email protected]','1992-01-31','M',1,'2018-04-09 16:51:30');

Alternatively you may specify "placeholder" values for the fields non-listed in VALUES clause. NULL or DEFAULT for autoincrement and DEFAULT for updated_at:

INSERT INTO clients 
VALUES 
(NULL,'Maria Dolores Gomez','Maria [email protected]','1971-06-06','F',1,'2018-04-09 16:51:30',DEFAULT),
(NULL,'Adrian Fernandez','[email protected]','1970-04-09','M',1,'2018-04-09 16:51:30',DEFAULT),
(NULL,'Maria Luisa Marin','Maria [email protected]','1957-07-30','F',1,'2018-04-09 16:51:30',DEFAULT),
(NULL,'Pedro Sanchez','[email protected]','1992-01-31','M',1,'2018-04-09 16:51:30',DEFAULT);

PS. I prefer the former method. The latter is affected by any table structure change, whereas the former may stay unchanged if you'll alter fields order in table structure or add NULLable fields into it.

Upvotes: 3

Harjinder
Harjinder

Reputation: 32

I hope this one help you :-

insert into clients(`name`,`email`,`birthdate`,`gender`,`active`,`created_at`,`updated_at`) VALUES
('Maria Dolores Gomez','[email protected]','1971-06-06','F',1,'2018-04-09 16:51:30','2018-04-09 16:51:30');

Upvotes: 0

seunggabi
seunggabi

Reputation: 1820

Add fields.

INSERT INTO clients (name, email, birthdate, gender, active, created_at) VALUES 
('Maria Dolores Gomez','Maria [email protected]','1971-06-06','F',1,'2018-04-09 16:51:30'),
('Adrian Fernandez','[email protected]','1970-04-09','M',1,'2018-04-09 16:51:30'),
('Maria Luisa Marin','Maria [email protected]','1957-07-30','F',1,'2018-04-09 16:51:30'),
('Pedro Sanchez','[email protected]','1992-01-31','M',1,'2018-04-09 16:51:30');

Upvotes: 3

Related Questions