Why does the update in my stored proc put null values into my table?

I've created a stored procedure in MySQL that: 1.) Creates a temp table, 2.) Populates some of its values 3.) Then attempts to update one of the fields with a statement like the following:

"UPDATE tempTable tt SET tempTablefield = SELECT tableBField WHERE etc...."

The first part of the stored proc fills the temp table with data and then that is followed by an update statement to update one of the fields in the temp table. I've looked at the table to see what it looks like before the update and the field that is getting update is full of the default values (see stored proc below). However, after the update the affected field contains null values in the rows where there is no match and this brings me to my question: Why does my update replace the original default value with null when it updates? Why does the update not only affect the matching rows?

CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_v1_ArchiveReport`()
BEGIN
DROP TABLE IF EXISTS utarchive;

CREATE TABLE utarchive (studentid VARCHAR(8), 
						studentname VARCHAR(50), 
                        weekTotal TIME DEFAULT '00:00:00', 
						reqHrs INT, 
                        sportTeamNm VARCHAR(25))
	ENGINE=INNODB;
    
    INSERT INTO utarchive (studentid, studentname, reqHrs, sportTeamNm)
		SELECT 
			uai.AthleteUid, 
            uai.AthleteFullName,
            uai.RequiredHours,            
            (SELECT utsportteam.SportTeamName FROM utsportteam WHERE utSportTeamUid = uai.Sportuid) AS SportTeamName         
        FROM 
			utathleteinfo uai	
		GROUP BY
			uai.AthleteUid;
        
        UPDATE utarchive uta SET weekTotal = 
        (SELECT
			COALESCE(SEC_TO_TIME(TIMESTAMPDIFF(MINUTE, uio.TimeIn, uio.TimeOut)), '00:00:00')  
		FROM 
			utinout uio
		WHERE
			uio.StudentID = uta.studentid AND
			uio.TimeIn >= '2018-02-05 06:00:00' AND uio.TimeOut <= '2018-02-07 23:59:59'
		GROUP BY
			uio.StudentID);
				
	SELECT * FROM utarchive;    
END

Open in new window


If I comment out (remove) the Update portion of the stored proc the data (a sample of it) looks like
before/without the update
when i put the update into the stored proc the data looks like this
after including the update
LVL 1
Michael SterlingWeb Applications DeveloperAsked:
Who is Participating?
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Johnsone has been on the right track with adding a where clause, but the where clause needs to restrict to the same row set as the  subselect, that is include the date range.
UPDATE utarchive uta 
SET    weektotal = (SELECT Coalesce(Sec_to_time(Timestampdiff(minute, 
                                                uio.timein, 
                                                       uio.timeout)), 
                                              '00:00:00' 
                           ) 
                    FROM   utinout uio 
                    WHERE  uio.studentid = uta.studentid 
                           AND uio.timein >= '2018-02-05 06:00:00' 
                           AND uio.timeout <= '2018-02-07 23:59:59' 
                    GROUP  BY uio.studentid) 
WHERE  EXISTS (
               SELECT *
                 FROM utinout uio
                WHERE uio.studentid = uta.studentid 
                  AND uio.timein >= '2018-02-05 06:00:00' 
                  AND uio.timeout <= '2018-02-07 23:59:59'
              );

Open in new window

0
 
Ares KurkluSoftware EngineerCommented:
May be good to see the  output of the internal query, what is being returned from utinout
SELECT
                  COALESCE(SEC_TO_TIME(TIMESTAMPDIFF(MINUTE, uio.TimeIn, uio.TimeOut)), '00:00:00')  
            FROM
                  utinout uio
            WHERE
                  uio.TimeIn >= '2018-02-05 06:00:00' AND uio.TimeOut <= '2018-02-07 23:59:59'
            GROUP BY
                  uio.StudentID
1
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
coalesce
the studentid matches or would match the studentid field in the archive temp table...
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Ares KurkluSoftware EngineerCommented:
Possible that the studentids are not matching in the
                  uio.StudentID = uta.studentid AND
line I can see that studentid in the temporary table is created as VARCHAR(8)
is it possible that there may be an issue when uai.AthleteUid is being inserted there?
1
 
johnsoneSenior Oracle DBACommented:
It updates every record in your temp table, even when there is no match, because you told it to.  You have no limiting clause on the update.  You need to add a where clause to the update to tell it to only update rows where there is a record in the second table.
UPDATE utarchive uta 
SET    weektotal = (SELECT Coalesce(Sec_to_time(Timestampdiff(minute, 
                                                uio.timein, 
                                                       uio.timeout)), 
                                              '00:00:00' 
                           ) 
                    FROM   utinout uio 
                    WHERE  uio.studentid = uta.studentid 
                           AND uio.timein >= '2018-02-05 06:00:00' 
                           AND uio.timeout <= '2018-02-07 23:59:59' 
                    GROUP  BY uio.studentid) 
WHERE  EXISTS (SELECT 1 
               FROM   utinout uio2 
               WHERE  uio2.studentid = uta.studentid); 

Open in new window

1
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
@johnsone: I put into place your suggestion, but am still getting NULL values for some of the records. I do get that it updates every record though, so I thought that my COALESCE statement would take care of that?
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
Thank you to all who contributed. Also to fix my NULL value issue, I simply added an UPDATE statement that went through my temp table and set that value to a time value of 00:00:00 where that value was NULL. Not how I wanted to do it but, it worked.
0
 
johnsoneSenior Oracle DBACommented:
COALESCE would not take care of it if there was no matching record from the subquery.  If the subquery returns no rows, then there is nothing to COALESCE.

Are you sure that the update is setting the value to NULL and it wasn't NULL to start with?
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
@johnsone: I tested the stored proc. incrementally. Before the update every row in my temp table had a value of: 00:00:00.
0
 
johnsoneSenior Oracle DBACommented:
Can you post a small test case that shows the issue?  Dummy data is fine.
0
 
johnsoneSenior Oracle DBACommented:
Thanks Qlemo.  Not sure how I missed the date restriction.  That should be the last piece of the puzzle.
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
I will play around with this some tonight and or this weekend. I did some preliminary testing and it seemed to work but I need to work it completely into my proc and test it out.
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
Thanks for the input and code suggestion!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.