Instructor table

Teaches table

I tried this

update instructor 
set salary = 10000 * (
select count(distinct sec_id, semester, year)
from teaches 
where instruction.ID = teaches.ID);

CodePudding user response：

I don't know if below approach is your expected result but it might give you a hint if is not the solution to your problem.

CREATE TABLE instructor  (
  id int,
  name varchar(25),
  dept_name varchar(50),
  salary decimal(10,2) );

INSERT INTO instructor VALUES
(10101,'Srinivasan',"Comp. Sci.",65000.00),
(12121,'Wu','Finance',90000.00),
(15151,'Mozart','Music',40000.00),
(22222,'Einstein','Physics',95000.00),
(32343,'El Said','History',60000.00),
(33456,'Gold','Physics',87000.00),
(45565,'Katz','Comp. Sci.',75000.00),
(58583,'Califieri','History',62000.00),
(76543,'Singh','Finance',80000.00),
(76766,'Crick','Biology',72000.00),
(83821,'Brandt','Comp. Sci.',92000.00),
(98345,'Kim','Elec. Eng.',80000.00);


CREATE TABLE teaches (
  id int,
  course_id varchar(25),
  sec_id int,
  semester varchar(20),
  year int  );

INSERT INTO teaches VALUES
(76766,'BIO-101',1,'Summer',2017),
(76766,'BIO-301',1,'Summer',2018),
(10101,'CS-101',1,'Fall',2017),
(83821,'CS-190',1,'Spring',2017),
(83821,'CS-190',2,'Spring',2017),
(83821,'CS-319',2,'Spring',2018),
(10101,'CS-347',1,'Fall',2017),
(98345,'EE-18',1,'Spring',2017),
(22222,'PHY-101',1,'Fall',2017);


update instructor i
inner join 
      (  select id,
                count(distinct course_id)  as cource_id_count
         from teaches 
         group by id
      ) as t1 on t1.id=i.id 
set i.salary= 10000 * t1.cource_id_count;

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=2a33159d87254d3a39535ccd9bf542d3

Check for more details on update :https://dev.mysql.com/doc/refman/5.6/en/update.html