Update command error in MySQL

Question

I'm trying to update the attendance of a student given the University Seat Number(USN) and the Subject. I used the following query and it says none of the rows were affected.

Update `Student Attendance` as sa 
JOIN Subjects as s ON sa.`Subject Code`=s.`Subject Code` 
set sa.Attendance ='5' AND sa.`Absent Days`='2017-10-12' 
where sa.USN ='1ks15bt001' AND s.Subject='English';

When I ran the the below query alone I got this as the output :

  Select sa.USN,s.Subject,sa.Attendance
  From `Student Attendance` as sa 
  JOIN Subjects as s ON sa.`Subject Code`=s.`Subject Code` 
  where sa.USN ='1ks15bt001' AND s.Subject='English';

Schema of Student Table :

CREATE TABLE `Student` (
  `USN` varchar(10) NOT NULL,
  `DOB` date NOT NULL,
  `Dep` varchar(3) NOT NULL,
  `SEM` int(1) NOT NULL,
  `Class` varchar(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `Student`
  ADD PRIMARY KEY (`USN`),
  ADD KEY `Dep` (`Dep`);

Student Attendance :

CREATE TABLE `Student Attendance` (
  `USN` varchar(10) NOT NULL,
  `Subject Code` varchar(6) NOT NULL,
  `Attendance` int(11) NOT NULL DEFAULT '0',
  `Absent Days` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `Student Attendance`
  ADD PRIMARY KEY (`USN`,`Subject Code`),
  ADD KEY `ABC` (`Subject Code`);

Subjects :

CREATE TABLE `Subjects` (
  `Subject` varchar(40) NOT NULL,
  `Subject Code` varchar(6) NOT NULL,
  `Dep` varchar(3) NOT NULL,
  `Sem` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `Subjects`
  ADD PRIMARY KEY (`Subject Code`,`Subject`) USING BTREE,
  ADD KEY `bgf` (`Dep`);

My bad, I don't think that will affect the query as that's just a String. — SaberSz
– SaberSz, Commented Nov 4, 2017 at 22:08
A schema like this appears to violate the Zero, One or Infinity Rule of database normalization by having multiple date values in a single field. You should create a proper one-to-many relationship so that the number of days here can be properly relational, plus not limited by arbitrary field length constraints. — tadman
– tadman, Commented Nov 4, 2017 at 22:18

Gordon Linoff · Accepted Answer · 2017-11-04 22:12:17Z

1

The correct syntax is:

Update `Student Attendance` sa join
       Subjects s 
       on sa.`Subject Code` = s.`Subject Code` 
    set sa.Attendance = 5,
        sa.`Absent Days`= '2017-10-12' 
    where sa.USN = '1ks15bt001' AND s.Subject = 'English';

Note that Attendance is a number, so I removed the single quotes.

Your set clause is parsed as:

set sa.Attendance = ('5' AND sa.`Absent Days` = '2017-10-12')

This is a boolean expression that returns 0 for false and 1 for true. 0 would indicate that it is false for the row in question.

answered Nov 4, 2017 at 22:12

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update command error in MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related