-1
голос «против»
избранное
Есть три таблицы:

CREATE TABLE departments (
id INT NOT NULL,
departments_name varchar (50) NULL,
PRIMARY KEY (id)
);



CREATE TABLE employees (
id int NOT NULL,
first_name varchar(50) NULL,
last_name varchar(50) NULL,
department_id int NULL,
salary money NULL,
PRIMARY KEY (id),
FOREIGN KEY (department_id) REFERENCES departments (id)
);



CREATE TABLE sales (
employee_id int NOT NULL,
sales_time datetime NOT NULL,
amount money NULL,
PRIMARY KEY (employee_id, sales_time),
FOREIGN KEY (employee_id) REFERENCES employees (id)
);





Есть три таблицы:

CREATE TABLE departments (
id INT NOT NULL,
departments_name varchar (50) NULL,
PRIMARY KEY (id)
);



CREATE TABLE employees (
id int NOT NULL,
first_name varchar(50) NULL,
last_name varchar(50) NULL,
department_id int NULL,
salary money NULL,
PRIMARY KEY (id),
FOREIGN KEY (department_id) REFERENCES departments (id)
);



CREATE TABLE sales (
employee_id int NOT NULL,
sales_time datetime NOT NULL,
amount money NULL,
PRIMARY KEY (employee_id, sales_time),
FOREIGN KEY (employee_id) REFERENCES employees (id)
);
Обновить значение salary в таблице employees установив заработные платы для сотрудников по следующим правилам:
Если выручка меньше 10000, но больше чем 20 * salary сотрудника - умножить значение salary на 1.5.

Мой запрос:

UPDATE employees SET
salary = salary * 2
WHERE EXISTS (
SELECT 1
FROM sales s
WHERE s.employee_id = employees.id AND
YEAR(CAST(sales_time AS DATE)) = 2017
group by s.employee_id
HAVING sum(s.amount) < 10000 AND sum(s.amount) > (
SELECT salary * 20
FROM employees e
where e.id = s.employee_id)
)
Обрабатывает 0 строк, а должна быть одна

Руслан Дамирович,
да, спасибо)