Ошибка 1111 sql

27 июля 2009 г.

MySQL

Не возможно использовать для обновления таблицу, в которой производишь выборку

Понадобилось ежесуточно высчитывать размер шрифта для оформления вывода жанров на Кинсбурге. И столкнулся с проблемой, что нельзя выбирать данные из таблицы, которая участвует в обновлении данных.

Вот примеры запросов

Пробуем в лоб:

update `categories` set `size` = (`count` / ((max(`count`) - min(`count`)) / 10));
ERROR 1111 (HY000): Invalid use of group function

Попробуем вложенный селект:
update `categories` set `size` = (select (`count` / ((max(`count`) - min(`count`)) / 10)) from `categories`);
ERROR 1093 (HY000): You can't specify target table 'categories' for update in FROM clause

Попробуем жоин:
update `categories` as `c1` 
JOIN `categories` as `c2` 
using(`category_id`) 
set `c1`.`size` = (`c2`.`count` / ((max(`c2`.`count`) - min(`c2`.`count`)) / 10));

ERROR 1111 (HY000): Invalid use of group function

По отдельности все работает
Выводим «размер шрифта»:
select (`count` / ((max(`count`) - min(`count`)) / 10)) from `categories`;
+--------------------------------------------------------+
| (`count` / ((max(`count`) - min(`count`)) / 10))     |
+--------------------------------------------------------+
|                                             3.47826087 |
+--------------------------------------------------------+
1 row in set (0.00 sec)

Обновляем поле с размером шрифта:
update `categories` set `size` = 3.47826087;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 21  Changed: 0  Warnings: 0

Решение
Погуглив, и поломав голову с disc’ом, я пришел к следующему решению, представленное ниже.
Я решил выделить вычисление процента в переменную @percent, далее создал вьюху для таблицы «categories» и жойню таблицу с вьюхой:



  


                       
  




-- создаем коэффициент деления
set @percent = (select (max(`count`) - min(`count`)) / 10 from `categories`);

-- создаем вьюху
create view `categories_view` as select `category_id`, `count` from `categories`;

-- жойним таблицу и вьюху, обновляя данные
update `categories` as `c`
join `categories_view` as `cv`
using(`category_id`)
set `c`.`size` = `cv`.`count` / @percent;

Вот и все, приятного манокурения   
UPD: Создадим процедуру и евент для этого события
/* Создаем вьюху и процедуру для установки размеров шрифта */
use kinsburg;

/* создаем вьюху */
CREATE VIEW `categories_view` AS SELECT `category_id`, `count` FROM `categories`;

/* создаем процедуру */
delimiter //
DROP PROCEDURE IF EXISTS `updateCategorySize`//
CREATE PROCEDURE `updateCategorySize` ()
BEGIN
    /* создаем коэффициент деления */
    SET @percent = (SELECT (max(`count`) - min(`count`)) / 10 FROM `categories`);
    /* жойним таблицу и вьюху, обновляя данные */
    UPDATE `categories` AS `c` JOIN `categories_view` AS `cv` USING(`category_id`) SET `c`.`size` = `cv`.`count` / @percent;
END//
delimiter ;

/* создаем евент для вызова процедуры раз в сутки */
CREATE
    DEFINER = kinsburg@localhost
    EVENT `updateCategorySizeEvent`
    ON SCHEDULE
      EVERY 1 DAY
    DO
      CALL updateCategorySize; 

To correctly use aggregate function with where clause in MySQL, the following is the syntax −

select *from yourTableName
where yourColumnName > (select AVG(yourColumnName) from yourTableName);

To understand the above concept, let us create a table. The query to create a table is as follows −

mysql> create table EmployeeInformation
   -> (
   -> EmployeeId int,
   -> EmployeeName varchar(20),
   -> EmployeeSalary int,
   -> EmployeeDateOfBirth datetime
   -> );
Query OK, 0 rows affected (1.08 sec)

Now you can insert some records in the table using insert command. The query is as follows −

mysql> insert into EmployeeInformation values(101,'John',5510,'1995-01-21');
Query OK, 1 row affected (0.13 sec)
mysql> insert into EmployeeInformation values(102,'Carol',5600,'1992-03-25');
Query OK, 1 row affected (0.56 sec)
mysql> insert into EmployeeInformation values(103,'Mike',5680,'1991-12-25');
Query OK, 1 row affected (0.14 sec)
mysql> insert into EmployeeInformation values(104,'David',6000,'1991-12-25');
Query OK, 1 row affected (0.23 sec)
mysql> insert into EmployeeInformation values(105,'Bob',7500,'1993-11-26');
Query OK, 1 row affected (0.16 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from EmployeeInformation;

The following is the output −

+------------+--------------+----------------+---------------------+
| EmployeeId | EmployeeName | EmployeeSalary | EmployeeDateOfBirth |
+------------+--------------+----------------+---------------------+
|        101 | John         |           5510 | 1995-01-21 00:00:00 |
|        102 | Carol        |           5600 | 1992-03-25 00:00:00 |
|        103 | Mike         |           5680 | 1991-12-25 00:00:00 |
|        104 | David        |           6000 | 1991-12-25 00:00:00 |
|        105 | Bob          |           7500 | 1993-11-26 00:00:00 |
+------------+--------------+----------------+---------------------+
5 rows in set (0.00 sec)

Here is the correct way to use aggregate with where clause. The query is as follows −

mysql> select *from EmployeeInformation
   -> where EmployeeSalary > (select AVG(EmployeeSalary) from EmployeeInformation);

The following is the output −

+------------+--------------+----------------+---------------------+
| EmployeeId | EmployeeName | EmployeeSalary | EmployeeDateOfBirth |
+------------+--------------+----------------+---------------------+
|        105 | Bob          |           7500 | 1993-11-26 00:00:00 |
+------------+--------------+----------------+---------------------+
1 row in set (0.04 sec)