MySQL/SQL: обновление с коррелированным подзапросом из самой обновленной таблицы

У меня есть общий вопрос, который я попытаюсь объяснить с помощью примера.

Скажем, у меня есть таблица с полями: "id", "name", "category", "появления" и "ratio"

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

В псевдокоде мне нужно следующее:

  • Для каждой категории
    найдите общую сумму явлений для предметов, связанных с ней. Например, это можно сделать с помощью (select sum("appearances") from table group by category)

  • Для каждого элемента
    задайте значение отношения как значения элементов, деленные на сумму, найденную для категории выше

Теперь я пытаюсь достичь этого с помощью одного запроса на обновление, но, похоже, не может этого сделать. Я думал, что должен сделать:

update Table T    
set T.ratio = T.appearances /   
(    
select sum(S.appearances)    
from Table S    
where S.id = T.id    
)

Но MySQL не принимает псевдоним T в столбце обновления, и я не нашел других способов достижения этого.

Любые идеи?

Ответы

Ответ 1

После двух ответов, которые я получил (ни один из которых не был полным, поэтому я написал свои собственные), в конце концов я сделал следующее:

UPDATE Table AS target
INNER JOIN 
(
select category, appearances_sum
from Table T inner join (
    select category as cat, sum(appearances) as appearances_sum
    from Table
    group by cat
) as agg
where T.category  = agg.cat
group by category
) as source
ON target.category = source.category
SET target.probability = target.appearances / source.appearances_sum

Он работает очень быстро. Я также попытался с коррелированным подзапросом, но он был намного медленнее (на порядок), поэтому я придерживаюсь соединения.

Ответ 2

Используйте соединения сразу после UPDATE: Справочное руководство - 13.2.11 Синтаксис UPDATE

так UPDATE table1 внутреннее соединение table2 on.... set table1.foo = значение где table2.bla = someothervalue

С такими вещами всегда смотрите руководство. MySql имеет правильное справочное руководство, поэтому не должно быть так сложно получить правильный синтаксис;)

Ответ 3

Вот как это делается в mssql, я думаю, что mysql тот же или похожий:

create table T (id int, ratio float, appearances int)
insert T values (1, null, 2)
insert T values (1, null, 3)

update T
set ratio = cast(appearances as float)/ agg.appearancesSum
from T join (
    select id, sum(appearances) as appearancesSum
    from T
    group by id
) as agg on t.id = agg.id