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`()
DROP TABLE IF EXISTS utarchive;
CREATE TABLE utarchive (studentid VARCHAR(8),
weekTotal TIME DEFAULT '00:00:00',
INSERT INTO utarchive (studentid, studentname, reqHrs, sportTeamNm)
(SELECT utsportteam.SportTeamName FROM utsportteam WHERE utSportTeamUid = uai.Sportuid) AS SportTeamName
UPDATE utarchive uta SET weekTotal =
COALESCE(SEC_TO_TIME(TIMESTAMPDIFF(MINUTE, uio.TimeIn, uio.TimeOut)), '00:00:00')
uio.StudentID = uta.studentid AND
uio.TimeIn >= '2018-02-05 06:00:00' AND uio.TimeOut <= '2018-02-07 23:59:59'
SELECT * FROM utarchive;
If I comment out (remove) the Update portion of the stored proc the data (a sample of it) looks like
when i put the update into the stored proc the data looks like this