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

Базы данных 28.07.2009

mysql-logo

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

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

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

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;
Retweet | Оставить комментарий | Комментарии в RSS | Теги:

Назад: Инклуд конфигов Sphinx
Далее: Купил книгу по Python

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

» Трекбеки скрыты, показать их?
  1. dallone ↓ | 28.07.2009 в 10:56

    Adw0rd, Насколько я понимаю что после выполнения данной процедуры, что после желаемого изначально UPDATE, у нас будет стопицот категорий, с одним и тем же значением `size`.

    Внимание вопрос: «Зачем хранить одно и то же значение в 9342423 экземплярах? Как же нормализация?»

    ИМХО: по крону раз в те-же сутки меняем значение «.categories_cloud {

    font-size:...» в CSS файле и не усложняем зря жизнь себе и БД :)

    Ну а так да, вполне себе приятное решение.

  2. adw0rd ↓ | 28.07.2009 в 12:36

    Насколько я понимаю что после выполнения данной процедуры, что после желаемого изначально UPDATE, у нас будет стопицот категорий, с одним и тем же значением `size`

    Нет, не будет, я же проверяю сначала, прежде чем публиковать. ;)

  3. adw0rd ↓ | 28.07.2009 в 12:40

    Зачем хранить одно и то же значение в 9342423 экземплярах? Как же нормализация?

    вот что получается

    +-------------+-------------+-------+------+
    | category_id | url         | count | size |
    +-------------+-------------+-------+------+
    |           1 | action      |     8 |    3 |
    |           2 | serial      |    17 |    7 |
    |           3 | documentary |     0 |    0 |
    |           4 | cartoon     |    10 |    4 |
    |           5 | western     |     0 |    0 |
    |           6 | detective   |     3 |    1 |
    |           7 | drama       |    16 |    7 |
    |           8 | historical  |     7 |    3 |
    |           9 | comedy      |    23 |   10 |
    |          10 | comics      |     0 |    0 |
    |          11 | crime       |     4 |    2 |
    |          12 | romance     |     6 |    3 |
    |          13 | mistic      |     0 |    0 |
    |          14 | parody      |     0 |    0 |
    |          15 | adventure   |     8 |    3 |
    |          16 | sport       |     0 |    0 |
    |          17 | thriller    |     9 |    4 |
    |          18 | horror      |     2 |    1 |
    |          19 | fantastic   |     5 |    2 |
    |          52 | fantasy     |     2 |    1 |
    +-------------+-------------+-------+------+

    Никаких «9342423 экземплярах» ;)

  4. adw0rd ↓ | 28.07.2009 в 12:42

    И вот такое облако получаем:

    cloud

  5. dallone ↓ | 28.07.2009 в 21:08

    Нет, не будет, я же проверяю сначала, прежде чем публиковать. ;)

    Хм... Просто запрос:

    SELECT (`count` / ((max(`count`) - min(`count`)) / 10)) FROM `categories`;

    При данных:

    CREATE TABLE `categories` (
      `id` int(11) NOT NULL auto_increment,
      `size` int(11) NOT NULL default 0,
      `count` int(11) NOT NULL default 0,
      PRIMARY KEY  (`id`),
      KEY `count` (`count`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

    INSERT INTO `categories` (`id`, `count`) VALUES
    (1, 5),
    (2, 12),
    (3, 18),
    (8, 5),
    (11, 5),
    (12, 12),
    (13, 18),
    (18, 5),
    (19, 2);

    У меня лично выдает только одно значение — 1.2500 (Как я понимаю для последнего столбца с `id` 19 (MySQL5). Или я что-то недопонимаю?

    ИМХО все равно данные размера как таковые к категориям особого отношения не имеют, они грубо говоря связь облака тегов с категориями, да и юзаются они ведь только в нем, не так ли?

    Поэтому можно было бы сделать таблицу:

    CREATE TABLE `tag_cloud_size`(
    `category_id` INT NOT NULL,
    `size` INT NOT NULL DEFAULT 0,
    PRIMARY KEY(`category_id`)
    );

    Ну и в ней собственно все их(значения) хранить.

  6. adw0rd ↓ | 28.07.2009 в 21:47

    SELECT (`count` / ((max(`count`) - min(`count`)) / 10)) FROM `categories`;

    да, именно этот запрос сделает всем строкам один результат, но этот запрос только для теста, посмотри внимательно процедуру, там этот кусок кода:

    (max(`count`) - min(`count`)) / 10)

    вынесен в отдельную переменную @percent и все работает наура, для всех строк

  7. adw0rd ↓ | 28.07.2009 в 21:49

    ИМХО все равно данные размера как таковые к категориям особого отношения не имеют, они грубо говоря связь облака тегов с категориями, да и юзаются они ведь только в нем, не так ли?

    Не согласен, зачем мне делать доп. таблицу, если эти данные необходимы для генерации облака (титл, урл, размер)?

    Я считаю это лишним.

  8. adw0rd ↓ | 28.07.2009 в 21:50

    Поэтому можно было бы сделать таблицу:

    CREATE TABLE `tag_cloud_size`(
    `category_id` INT NOT NULL,
    `size` INT NOT NULL DEFAULT 0,
    PRIMARY KEY(`category_id`)
    );

    Ну и в ней собственно все их(значения) хранить.

    С тем же успехом можно было отделить «count» в другую таблицу.

  9. dallone ↓ | 28.07.2009 в 22:22

    `count` имеет отношение к категориям,

    `size` имеет отношение исключительно к облакам и нефиг ему делать в основной таблице.

    Впрочем на вкус и цвет товарищей нет. Проехали :)

    вынесен в отдельную переменную @percent и все работает наура, для всех строк

    Ну да я это понял, просто меня переклинило на первом запросе, стормозил признаю.

    P.S.

    Впринципе вполне логично, что они гененрят только одну строку ибо MAX, MIN это же агрегатные функции GROUP BY.

    Можно было бы заменить аццким запросом:

    SELECT `count`
            /
            (
              (SELECT MIN(`count`) FROM `categories`)
              -
              (SELECT MIN(`count`) FROM `categories`)
            / 10
            )

    FROM `categories`;

    Твой вариант лучше безусловно прозрачнее и быстрее, мне просто занятся нечем пока футурама скачивается.

  10. adw0rd ↓ | 28.07.2009 в 22:29

    `size` имеет отношение исключительно к облакам и нефиг ему делать в основной таблице.

    Ну смотри, для генерации облака нам нужно помимо «размера» еще «имя жанра» и «ссылка, либо ид жанра», так?

    Тогда логично чтобы они были в одной таблице, чтобы не делать лишнего жойна, так?

    Тогда куда мне всунуть «размер»? Или ты мне предлагаешь одинаковые данные хранить в двух таблицах?

    футурама скачивается.

    Скоро выложим на кинсбурге, заходи ;)

  11. Ad1ce ↓ | 29.07.2009 в 01:51

    1. Движок Кинсбурга самописный?

    2. Чем оформляется подсветка синтаксиса кода на этом блоге?

  12. adw0rd ↓ | 29.07.2009 в 01:58

    Ad1ce,

    1. Да, самописный

    2. Плагин Codecolorer

Оставить комментарий



Где купить Модемы в Красноярске сравнение цен