Update HSQL table values based on related table

Question

I have 2 related tables: "Universities"(id, num_courses) and "Courses" (id, university_id). I need University.num_courses to represent a COUNT of courses.

I can query data I need:

SELECT 
Universities.id,
COUNT(Courses.university_id) as NumCourses
FROM Courses
JOIN Universities ON Universities.id = Courses.university_id
GROUP BY Universities.id

How do I UPDATE Universities with it? Could it be done automatically?

It's generally bad database design to store a calculated value in a table - calculated values should be viewed and worked with through queries or views. Why are you wanting to do this? Perhaps we can suggest a better way to get the end use you're looking for. — Lyrl, Jan 11 '16 at 15:05
As far as "automatically" you could set up a `trigger` in your database if your back-end supports it. The default HSQLDB version 1.8 backend that comes zipped inside Base does *not* support triggers - you would need to upgrade to a 'split' (i.e. not zipped inside Base) backend of HSQLDB version 2 or some other database of your choice. — Lyrl, Jan 11 '16 at 15:09
@Lyrl I totally agree with you, but situation is complicated. Basically, I'm helping a friend who has a database project to make, and his teacher approved the design they have made together. He isn't so bright about databases (neither me) and fears to step away from the design they have agreed on, but in the same time has to implement those ineffective solutions. Maybe that's a part of the education meant to prepare him for corporate job =D — I Hafid, Jan 12 '16 at 18:06
The Base query window only supports SELECT statements. To execute an UPDATE statement use the Tools→SQL window. As the table "Courses" grows the UPDATE statement would need to be rerun periodically. Keeping the "Universities"."num_courses" column up to date in real time would require a database trigger (more robust) or a macro (tied to a form event, would work well as long as all changes were made through the form). — Lyrl, Jan 12 '16 at 18:21

score 1 · Accepted Answer · edited May 23 '17 at 11:44

I am not sure this is what you mean by "automatically," but it can be done with a loop:

Sub UpdateUniversities
    Context = CreateUnoService("com.sun.star.sdb.DatabaseContext")
    databaseURLOrRegisteredName = "file:///C:/Users/JimStandard/Desktop/New Database.odb"
    Db = Context.getByName(databaseURLOrRegisteredName )
    Conn = Db.getConnection("","") 'username & password pair - HSQL default blank
    Stmt = Conn.createStatement()
    strSQL = "SELECT Universities.id, " & _
        "COUNT(Courses.university_id) as NumCourses " & _
        "FROM Courses " & _
        "JOIN Universities ON Universities.id = Courses.university_id " & _
        "GROUP BY Universities.id"
    oResult = Stmt.executeQuery(strSQL)
    Do While oResult.next()
        university_id = oResult.getLong(1)
        num_courses = oResult.getLong(2)
        Stmt = Conn.createStatement()
        strSQL = "UPDATE ""Universities"" SET ""num_courses"" = " & _
            num_courses & " WHERE ""id"" = " & university_id
        Stmt.executeUpdate(strSQL)
    Loop
    Conn.close()
End Sub

Related: How to create an update query with Open Office Base?

Note this is a macro that would need to be run periodically or tied to some event, such as a pushbutton on a form. — Lyrl, Jan 12 '16 at 21:22

Update HSQL table values based on related table

1 Answers1

Linked