CODEWITHSUNDEEP
mysqlsql
meowmix
meowmix

Reputation: 1

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

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

Upvotes: 0

Views: 415

Answers (2)

JBrooks
JBrooks

Reputation: 10013

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,

)

Upvotes: 1

LEE Hau Chon
LEE Hau Chon

Reputation: 445

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.

Upvotes: 0

Related Questions