How to update all columns and rows from another table with a stored procedure

Question

I have a few tables for a college.

Apply (sID int(3), cName varchar(20), major varchar(20), decision char(1))
College (cName char(20), state char(2), enrollment int(11))
Student (sid char(6), sName char(20), GPA decimal(3,2), sizeHS int(11))

I created this table:

CollegeStats (cName varchar(20), appCount int(11), minGPA decimal(3,2), maxGPA decimal(3,2))

I need to create a stored procedure that updates CollegeStats, with no parameters based on Apply, College, and Student table.

I've worked on several iterations of code to try to come to an answer, and this is my latest one. I'd really appreciates any and all help.

No parameter for this process.

ROUTINE NAME: updateCollegeStatsAll

BEGIN
    UPDATE CollegeStats 
    SET appCount = (SELECT COUNT(*) FROM Apply),
        minGPA = (SELECT MIN(GPA) FROM Student),
        maxGPA = (SELECT MAX(GPA) FROM Student);
END

When I run this code it updates all the rows to be the same.

cName   appCount    minGPA  maxGPA  
-----------------------------------
Cornell  20         2.90    4.00    
MIT      20         2.90    4.00    
CALTEC   20         2.90    4.00    
Davis    20         2.90    4.00

Break the query down into its bits. Think about what (select count(*) from Apply) will return. It will return 20. Thus your Update is effectively Update CollegeStats Set appCount = 20, ... and the same for the rest of the columns. You need to look at how to do an update with a join. You might want to refer to this answer [How can I do an UPDATE statement with JOIN in SQL?](https://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql) — GMc, May 09 '19 at 02:44
Which RDBMS is this for? Please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. — marc_s, May 09 '19 at 05:06

score 1 · Answer 1 · answered May 09 '19 at 03:08

Data should only live in 1 place in the universe. Having a stored proc summarize data is a bad idea. A view is more appropriate for this problem.

All tables should have an ID column. Not sure from your question how the tables relate to each other. But if you had ID columns this is how your view would look:

CREATE VIEW vCollageStats AS
(
    SELECT c.cName,
        COUNT(DISTINCT a.Id) AS AppCount,
        MIN(s.GPA) AS MinGPA,
        MAX(s.GPA) AS MaxGPA
    FROM College c
    LEFT JOIN Apply a
    ON c.Id = a.CollegeId
    LEFT JOIN Student s
    ON a.StudentId = s.Id
    GROUP BY c.cName,

)

score 0 · Answer 2 · answered May 09 '19 at 02:37

Maybe you can try this.

/* Create a dummy table to store student and their respective college id */
DECLARE @stud_clg TABLE (sid char(6),GPA decimal(3,2),cName varchar(20))
INSERT INTO @stud_clg 
SELECT A.sid, A.GPA, B.cName FROM 
Student A INNER JOIN Apply B 
ON A.sid = B.sID

/* Create a dummy table to store college and their student count */
DECLARE @clgstudct TABLE (cName varchar(20), ct int(11))
INSERT INTO @clgstudct
SELECT cName,COUNT(*) FROM @stud_clg 
GROUP BY cNAME

/* Update your actual table using WHILE loop */
DECLARE @ctrow int = (SELECT COUNT(*) FROM @clgstudct)
DECLARE @studct int(11)
DECLARE @clgname varchar(20)

/* Begin While loop */
WHILE @ctrow != 0
BEGIN
SET @studct = (SELECT TOP 1 ct FROM @clgstudct)
SET @clgname = (SELECT TOP 1 cNAME FROM @clgstudct)
DELETE FROM @clgstudct WHERE cName = @clgname

/* Update statement */
UPDATE CollegeStats SET
appCount = @studct,
minGPA = (SELECT MIN(GPA) FROM @stud_clg WHERE cName = @clgname),
maxGPA = (SELECT MAX(GPA) FROM @stud_clg WHERE cName = @clgname)
WHERE cName = @clgname
/* End Update statement */

SET @ctrow = (SELECT COUNT(*) FROM @clgstudct)
END
/* End While loop */

Because I don't have actual record for your table (and I am too lazy to insert dummy data to test), I code this without using any compiler and just based on my logic. I hope it works for you. If any problem faced, please leave a comment, I will try to fix it. This approach might seem too long, I think there should be a simpler solution.

Additionally, I think that this kind of logic should be done using programming language like PHP, VB or C# instead of using SQL. As an example, you can call an UPDATE query inside your PHP loop for every colleges.

This can easily be done with a single UPDATE or MERGE statement (at least in most modern DBMS). No loops, variables or other procedural elements required. Btw: your answer doesn't use "SQL" - it uses Microsoft's T-SQL (it's still unclear which DBMS product meowmix is using) — , May 09 '19 at 07:06

How to update all columns and rows from another table with a stored procedure

2 Answers2