'Error Code: 1136. Column count doesn't match value count at row 1' and yet it does match

Question

I've run into this error recently, and it's stalling my progress. Despite my values matching the number of columns, it is still displaying the error and I'm well and truly stumped.

my table is as follows:

DROP TABLE IF EXISTS `mydb`.`subject` ;

CREATE TABLE subject (
SubjectId VARCHAR(9) NOT NULL,
Units INT NOT NULL,
Requirements VARCHAR(150) NOT NULL,
Capacity INT NOT NULL,
StudentId INT NOT NULL,
Grades VARCHAR(30) NOT NULL,
EnrollmentID VARCHAR(10) NOT NULL,
DurationHours INT NOT NULL,
Cost INT NOT NULL,
PRIMARY KEY (SubjectId),
FOREIGN KEY (`StudentId`)
REFERENCES `mydb`.`student` (`StudentId`),
CONSTRAINT `fk_subject_enrollment1`
FOREIGN KEY (`EnrollmentID`)
REFERENCES `mydb`.`enrollment` (`EnrollmentId`)
    );

My insert statements are as follows:

INSERT INTO subject (SubjectId, Units, Requirements, Capacity, StudentId, Grades, EnrollmentId, DurationHours, Cost) 
 VALUES 
 ( 'ICTWEB567', '10', 'complete all units to satisfactory standard', '4334', '789089076', 'satisfactory 65/100', 'pegsue5687', '20', '5000');
 
 INSERT INTO subject (SubjectId, Units, Requirements, Capacity, StudentId, Grades, EnrollmentId, DurationHours, Cost) 
 VALUES 
 ( 'ICTWEB899', '13', 'complete all units to satisfactory standard', '1560' '789089076', 'satisfactory 100/100', 'dangia4965', '13', '3000');
 
 INSERT INTO subject (SubjectId, Units, Requirements, Capacity, StudentId, Grades, EnrollmentId, DurationHours, Cost) 
 VALUES 
 ( 'ICTWEB244', '12', 'complete all units to satisfactory standard','1000', '789089076', 'satisfactory 69/100', 'leegun3465', '18', '4000');
 
 INSERT INTO subject (SubjectId, Units, Requirements, Capacity, StudentId, Grades, EnrollmentId, DurationHours, Cost) 
 VALUES 
 ( 'ICTWEB442', '9', 'complete all units to satisfactory standard', '10000', '789089076', 'satisfactory 65/100', 'harkan5690', '15', '8000');
 
 INSERT INTO subject (SubjectId, Units, Requirements, Capacity, StudentId, Grades, EnrollmentId, DurationHours, Cost) 
 VALUES 
 ( 'ICTWEB567', '8', 'complete all units to satisfactory standard', '2000', '789089076', 'satisfactory 80/100', 'donsal8774', '17', '8000');

I have browsed as many pre-existing forums on here and elsewhere as I could find, and yet I have not arrived at a solution tailored to my issue. I have heard a lot about misplaced triggers and I have checked my triggers using the 'SHOW TRIGGERS' statement and nothing appears. I'm at a loss as to what's causing this.

Any help very much appreciated.

Missing comma in second insert statement - `'1560' '789089076'`. Also `ICTWEB567` is duplicated. — user1191247, Jun 07 '23 at 09:14
You don't need `INSERT INTO subject` multiple times either. That probably added to confusion. `VALUES (),(),();` is valid, That also would tell you appropriate line error was on. — user3783243, Jun 07 '23 at 09:20

score 0 · Accepted Answer · answered Jun 07 '23 at 09:14

You have a missing commas after '1560' in the second insert statement. While typically we would just close a typo question, I will expound here on what I did to debug your script and pinpoint the problem. I took these steps, running each INSERT statement one by one:

Add the CREATE TABLE statement followed by just the first INSERT statement. I observed that all code ran without error.
Now add the second INSERT statement. I observed that the code was now failing.
Next, I commented out the first INSERT statement, such that only the second INSERT was running. The failure persisted.
Having isolated the problem to the second INSERT, I then looked closely and discovered a missing comma in the VALUES clause.

I can't believe I missed that. late nights are tough. sorry for the redundant question. (btw please don't downvote me I'm already on thin ice) — Kudos, Jun 07 '23 at 09:25

'Error Code: 1136. Column count doesn't match value count at row 1' and yet it does match

1 Answers1