mySQL UPDATE表基于SELECT（count）的不同表

I have a table of classes, and a table of subjects.

我有一个班级表和一个科目表。

CLASS
|class_id|class_name|subject_id|date_time|
------------------------------------------
(imagine some rows here)

SUBJECT
|subject_id|subject_name|current_class_count|
---------------------------------------------
(imagine some rows here)

For indexing purposes I'd like to update the list of subjects every hour with the list of classes which have a date_time greater than right now.

为了建立索引,我想每小时使用date_time大于现在的类列表更新主题列表。

I can do a select statement like this:

我可以这样做一个select语句:

SELECT count(*) AS num, subject_id
FROM class
GROUP BY subject_id
where date_time > NOW()

and I will get something like

我会得到类似的东西

RESULT
|num|subject_id|
----------------
| 8 |        1 |
| 6 |        2 |
| 9 |        3 |
----------------

What's the most efficient way to get the subject table updated with the current_class_count? I could do this with PHP by looping through and doing several update statements, but I think mySql should have an easier way.

使用current_class_count更新主题表的最有效方法是什么?我可以通过循环并执行几个更新语句来使用PHP来完成此操作,但我认为mySql应该有一个更简单的方法。

1 个解决方案

#1

Edit: How about this:

编辑:这个怎么样:

UPDATE SUBJECT
LEFT JOIN (
SELECT count(*) AS num, subject_id
FROM class
GROUP BY subject_id
where date_time > NOW()) AS t ON SUBJECT.subject_id = t.subject_id
SET SUBJECT.current_class_count = coalesce( t.num, 0 )

As long as I've typed it right, basically you should be able to run this once an hour and it will update your SUBJECT table.

只要我输入正确,基本上你应该能够每小时运行一次,它会更新你的SUBJECT表。

Joining a table in an UPDATE statement is a bit different in MySQL compared to Microsoft SQL. Here is a link about it:

与Microsoft SQL相比,在UPDATE语句中加入表有点不同。这是一个关于它的链接:

http://blog.ookamikun.com/2008/03/mysql-update-with-join.html

#1