Update Query not Executing
I may have posted this in the incorrect forum previously, so please bear with me if you have come across this post before.
A simple update query of mine is not executing and I cannot trace the problem. By formulating the query using different WHERE criteria I can get it to execute, but the problem is not workable for more complex update operations.
For instance, the following query does not work:
UPDATE ResourceTbl
SET Rate = Rate * 1.1
WHERE ResName = 'nurse';
whereas the following query does work:
UPDATE ResourceTbl
SET Rate = Rate * 1.1
WHERE ResNo = 'R103';
According to my Course lecturer, the first query is the correct answer to the problem posed in the lecture. There are a number of other 'solutions' which are not executing, but the above is illustrative of all the difficulties I am encountering.
Here is the table and data set I am working with:
CREATE TABLE ResourceTbl(
ResNo varchar(8) not null,
ResName varchar(30) not null,
Rate decimal(12,2) not null,
CONSTRAINT ResourceTblPK PRIMARY KEY (ResNo),
CHECK (Rate>0) );
Insert into ResourceTbl (RESNO,RESNAME,RATE) values ('R100','attendant',10);
Insert into ResourceTbl (RESNO,RESNAME,RATE) values ('R101','police',15);
Insert into ResourceTbl (RESNO,RESNAME,RATE) values ('R102','usher',10);
Insert into ResourceTbl (RESNO,RESNAME,RATE) values ('R103','nurse',20);
Insert into ResourceTbl (RESNO,RESNAME,RATE) values ('R104','janitor',15);
Insert into ResourceTbl (RESNO,RESNAME,RATE) values ('R105','food service',10);
I am working with MySQL Workbench 6.3, Version 6.3.6 build 511 CE (64 bits)