2
DROP TABLE IF EXISTS student;

CREATE  TABLE student(
  bannerid VARCHAR(9) PRIMARY KEY NOT NULL ,
  lastname VARCHAR(45) NOT NULL ,
  firstname VARCHAR(45) NOT NULL ,
  major VARCHAR(45) NOT NULL DEFAULT 'undeclared' ,
  gpa DECIMAL(2) NOT NULL DEFAULT 0.00 ,
  age INT NOT NULL DEFAULT 18 );

  INSERT INTO student VALUES ('b00001111', 'smith', 'fred', 'computer science', 3.12, 20);
  INSERT INTO student VALUES ('b00002222', 'jones', 'herb', 'computer science', 2.00, 19);
  INSERT INTO student VALUES ('b00003333', 'chan', 'jackie', 'computer information systems', 3.50, 50);
  INSERT INTO student VALUES ('b00004444', 'baker', 'al');
  INSERT INTO student VALUES ('b00005555', 'booker', 'sue');

This results in the following error and I do not understand why. I want the last two INSERTs to use the default values.

MySQL Error 1136:Column count does not match value count at row 1

kmaz13
  • 260
  • 1
  • 5
  • 14
  • INSERT INTO student VALUES ('b00004444', 'baker', 'al', major, gpa, age); INSERT INTO student VALUES ('b00005555', 'booker', 'sue', major, gpa, age); – kmaz13 Mar 21 '12 at 04:04

1 Answers1

9

In the insert statement you need to specify the column names explicitly for the values that are being passed , you can ignore the column names if you are passing all the column values. So your last two statements should be 

INSERT INTO student (bannerid,lastname,firstname) VALUES ('b00004444', 'baker', 'al');
INSERT INTO student (bannerid,lastname,firstname) VALUES ('b00005555', 'booker', 'sue');
Rocky
  • 941
  • 7
  • 11
  • I'll put the check on your answer in 5 minutes... Thanks again :) – kmaz13 Mar 21 '12 at 04:05
  • 1
    If using `mysqldump` to import data from one table to another AND the destination table has a different number of columns, then include the `--complete-insert` option to add the column list to the `INSERT` statement(s). For example: `mysqldump --complete-insert -t -h MyHost -u MyUsername -pMyPassword MyDatabase > MyBackupFile.sql` – Arya Aug 18 '19 at 17:05