Updating salary query MySQL

Question

I want to make a query to update the salary of all doctors that have been practicing for more than 10 years, the new salary should correspond to a raise of 10 percent over the original salary, in the case of doctors with more than 100 consultations in the current year.

My database has these tables:

create table employee (
  employee_VAT varchar(255),
  employee_name varchar(255),
  employee_birth_date date,
  employee_street varchar(255),
  employee_city varchar(255),
  employee_ZIP varchar(255),
  employee_IBAN varchar(255),
  employee_salary numeric(20,2),
  primary key(employee_VAT),
  unique(employee_IBAN)
);
create table doctor (
  employee_VAT varchar(255),
  specialization varchar(255),
  biography text,
  email varchar(255) not null,
  primary key(employee_VAT),
  foreign key(employee_VAT) references employee(employee_VAT),
  unique(email)
);
create table appointment (
  doctor_VAT varchar(255),
  date_timestamp datetime, 
  appointment_description text,
  client_VAT varchar(255),
  primary key(doctor_VAT, date_timestamp),
  foreign key(doctor_VAT) references doctor(employee_VAT),
  foreign key(client_VAT) references client(client_VAT)
);
create table consultation (
  doctor_VAT varchar(255),
  date_timestamp datetime, 
  SOAP_S text,
  SOAP_O text,
  SOAP_A text,
  SOAP_P text,
  primary key(doctor_VAT, date_timestamp),
  foreign key(doctor_VAT, date_timestamp) references appointment(doctor_VAT, date_timestamp)
);

I have come close with this query I wrote:

update employee 
set employee_salary = employee_salary + 0.1 * employee_salary
where
  employee_VAT in (
    select pd.employee_VAT
    from permanent_doctor as pd
    where years >= 10
  )
  and employee_VAT in (
    select doctor_VAT
    from consultation 
    where doctor_VAT in (
      select doctor_VAT
      from consultation
      group by doctor_VAT having count(*) > 100
    )
  );

However this doesn't account for the consultations being in the current year like I want. I know that I'll probably have to incorporate year(date_timestamp) = year(now()) somewhere, but I don't know how. What am I missing here?

update your question add a proper data sample and the expected result — ScaisEdge, Dec 06 '19 at 20:05
You'd add it in your `select doctor_vat` subquery at the end, with a WHERE clause between the GROUP BY and HAVING clauses. — Ken White, Dec 06 '19 at 20:09
you can select your data that you want to update and then loop over that select to update the data , this https://stackoverflow.com/a/16350693/1838804 show how you can loop over a select result . — Mohammad Mirzaeyan, Dec 06 '19 at 20:09

score 1 · Answer 1 · edited Dec 06 '19 at 20:21

1

Add the timestamp restriction here

select doctor_VAT
from consultation
where year(date_timestamp) = year(now())
group by doctor_VAT having count(*) > 100

As long as the rest of your query is working as you want. Otherwise please provide some sample data with your expected result.

edited Dec 06 '19 at 20:21

Alessio Cantarella

5,077
3
27
34

answered Dec 06 '19 at 20:09

Error_2646

2,555
1
10
22

Updating salary query MySQL

1 Answers1