IMHO.WS - Req: запрос MySQL: обновление столбца одной таблицы данными из другой

IMHO.WS (http://www.imho.ws/index.php)

- Веб-программирование (http://www.imho.ws/forumdisplay.php?f=29)

Req: запрос MySQL: обновление столбца одной таблицы данными из другой

имеются две таблицы:
reviews, структура и примерное содержание:
ID RATING
2 3 (любое число)
1 5 (любое число)
3 2 (любое число)
2 9 (любое число)
1 3 (любое число)
и mlist:
NUM URATING
1
2
3
4

необходимо из первой таблицы найти среднее значение для каждого ID
(т.е. в данном примере это будет выглядеть так:
ID ROUND(avg(RATING))*1
2 6
1 4
3 2
и подставить полученные значения во вторую таблицу, чтоб получилось
NUM URATING
1 4
2 6
3 2
4 0

вопрос: как это можно сделать одним запросом? (как полагаю, здесь subqueries должны использоваться)

если бы были триггеры, можно было бы повесить на insert в первой таблице триггер, который бы обновлял данные во второй. а так, видимо, в два запроса.

insert
update

ок, а в два запроса это как? причем тут insert? убивать ни одну из строк (и уж тем более таблиц) нельзя! ибо в них не только эти данные содержатся (остальные столбцы убраны)

Видимо имелось в виду Select-Update
$sql="SELECT id, ROUND(avg(RATING)) as urating FROM reviews GROUP BY id ORDER BY id"
затем foreach строка в результате запроса:
UPDATE mlist SET URATING='$sql_f[urating]' WHERE num='$sql_f[id]'

Код:

UPDATE votes SET round = 

(  SELECT avg( rating )  FROM reviews WHERE id = 1 GROUP  BY id )  

WHERE id = 1

а вот так примерно выглядит триггер:

Код:

CREATE TRIGGER [dbo].[VotesUpdateTrigger] ON [dbo].[reviews] WITH EXECUTE AS CALLER AFTER  INSERT AS 

begin

        --check id

        if (exists(select v.id from votes v

        inner join inserted i 

        on v.id = i.id))

        begin

                UPDATE votes SET round = 

                (  SELECT avg( rating )  FROM reviews WHERE id = 1 GROUP  BY id )  

                WHERE id = 1

        end

        else

        begin

                insert into votes(id, round) select i.id, i.rating from inserted i

        end

end

p.s. весь код тестировался под ms sql server 2005 beta2:)

mysql 4.1 к сожалению нету(там бы работал вложенный запрос).

ну и наконец, в целом, мне кажется невыгодной такая структура в принципе, тем более под мускул.

имхо должно быть так:

rtable eviews

review_id, text

table votes

vote_id, review_id, vote_result

а оценку выводить уже по запросу(т.е. делать агрегат каждый раз, а не хранить его и обновлять постоянно).

Цитата:

Sinker:
Видимо имелось в виду Select-Update
$sql="SELECT id, ROUND(avg(RATING)) as urating FROM reviews GROUP BY id ORDER BY id"
затем foreach строка в результате запроса:

пока организовал это именно так, хотя это, имхо и не разумно (почему и задал это вопрос)

Цитата:

Sheryld:
а вот так примерно выглядит триггер:

триггер тоже уже был организован. Однако с его использованием есть проблема. Таблица mlist очень часто обновляется путем очищения её содержимого и upload'ом нового запроса sql из файла. Этот файл генерится программой, таким образом изменить его содержимое нельзя. Лишь возможные варианты - использовать команду UPDATE или INSERT. Если выбирать UPDATE, то тогда новые данные, появившиеся в этом файле в базу не попадают. Если INSERT - то будут дубли (прописать ON DUPLICATE увы нельзя). Таким образом, если триггер и срабатывает при добавлении нового/изменении старого значения в таблице reviews, то после убийства содержимого mlist все данные в последней теряются.

Цитата:

Sheryld:
ну и наконец, в целом, мне кажется невыгодной такая структура в принципе, тем более под мускул.

согласен. я и не хотел в таблице mlist делать отдельный столбец, хотел все данные в нужный момент цеплять из таблицы reviews. Но тут есть другая проблема - поскольку я организовал более-менее стандартный запрос к базе (чтобы создать один файл запроса, а потом уже ему передавать необходимые параметры), то чтобы добавить туда возможность использования еще одной таблицы его надо будет полностью переписать, что будет не просто.

не понял:

Код:

триггер тоже уже был организован. Однако с его использованием есть проблема. Таблица mlist очень часто обновляется путем очищения её содержимого и upload'ом нового запроса sql из файла. Этот файл генерится программой, таким образом изменить его содержимое нельзя. Лишь возможные варианты - использовать команду UPDATE или INSERT. Если выбирать UPDATE, то тогда новые данные, появившиеся в этом файле в базу не попадают. Если INSERT - то будут дубли (прописать ON DUPLICATE увы нельзя). Таким образом, если триггер и срабатывает при добавлении нового/изменении старого значения в таблице reviews, то после убийства содержимого mlist все данные в последней теряются.

я понимаю, что mlist полностью зависит от reviews. и служит только для хранения агрегата столбца записей из reviews?

кстати, покажи как ты создаешь таблицы, интересуют ключи и индексы. чтобы не было дубликатов можно сделать на столбце id в таблице mlist индекс unique или даже сделать его primary.

как именно происходит обновление mlist? в пакетном режиме?

также напиши свою версию СУБД.

Цитата:

Sheryld:
и служит только для хранения агрегата столбца записей из reviews?

нет, там еще куча других данных

Цитата:

кстати, покажи как ты создаешь таблицы, интересуют ключи и индексы. чтобы не было дубликатов можно сделать на столбце id в таблице mlist индекс unique или даже сделать его primary.

id так и есть unique

Цитата:

как именно происходит обновление mlist? в пакетном режиме?

пока - ручками... просто в phpmyadmin подгружаю сгенеренной программой файл...

Цитата:

также напиши свою версию СУБД.

нее, это не подходит :))) не тот проект, чтобы для него свою СУБД писать...

все гениальное - просто!
вот он, нужный мне запрос:

Код:

UPDATE mlist,reviews SET URATING=(SELECT ROUND(avg(reviews.RATING))*1 FROM reviews WHERE reviews.ID=mlist.NUM) WHERE reviews.ID=mlist.NUM

спасибо всем за помощь!