mySQL UPDATE table based on SELECT (count) of a different table

Question

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.

I can do a select statement like this:

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.

Have you looked into using triggers?

Jayy
– Jayy

2011-06-02 00:31:55 +00:00
Commented Jun 2, 2011 at 0:31 — Jayy
– Jayy, Commented Jun 2, 2011 at 0:31
Can you post actual tables definition?

sanmai
– sanmai

2011-06-02 01:43:59 +00:00
Commented Jun 2, 2011 at 1:43 — sanmai
– sanmai, Commented Jun 2, 2011 at 1:43

IAmTimCorey · Accepted Answer · 2011-06-02 02:42:01Z

2

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.

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

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

edited Jun 2, 2011 at 2:42

answered Jun 2, 2011 at 0:35

IAmTimCorey

16.8k5 gold badges42 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

cwd Over a year ago

I'm not positive but I don't think you can use a LEFT JOIN on an UPDATE command in mySQL. I tried it anyhow but I'm getting an "error near FROM SUBJECT"

IAmTimCorey Over a year ago

@cwd - my bad. I forgot MySQL was a bit different in the syntax. I updated my post and put a link on how the syntax works.

piotrm Over a year ago

It won't update subjects for which last class just ended, its count will stay indefinitely > 0. You really need left join and coalesce( t.num, 0 ) instead of t.num in the last line.

IAmTimCorey Over a year ago

@piotrm - Good point. I thought about that but then forgot to address it in my statement. I'll add it in.

piotrm Over a year ago

Almost perfect now, but still it has to be LEFT JOIN, coalesce is just to deal with NULL t.num that left join can possibly produce, which will happen exactly after passing max class date for given section or for sections without any class. I would also add index on (subject_id, date_time) in the class table and its the fastest possible, correct solution.

|

Collectives™ on Stack Overflow

mySQL UPDATE table based on SELECT (count) of a different table

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related