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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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
Qlemo"Batchelor", 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.