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

[LAndrew2]

имеются две таблицы:
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 должны использоваться)

[Sheryld]

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

insert
update

[LAndrew2]

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

[Sinker]

Видимо имелось в виду 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]'

[Sheryld]

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

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

[LAndrew2]

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. Но тут есть другая проблема - поскольку я организовал более-менее стандартный запрос к базе (чтобы создать один файл запроса, а потом уже ему передавать необходимые параметры), то чтобы добавить туда возможность использования еще одной таблицы его надо будет полностью переписать, что будет не просто.

[Sheryld]

не понял:

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

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

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

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

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

[LAndrew2]

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

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

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

id так и есть unique

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

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

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

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

[LAndrew2]

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

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

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