Moving this question from Stack Overflow to DBA.
I created a stored procedure to update missing data in a table, using the MySQL documentation as a guide. It works, however it seems to stop after one iteration. I can call the procedure again, and it will run through once successfully again then stop. Here is a simplified (and seemingly redundant) example of my tables.
Table departments
+----+-----+-----+----------+
|ntid|fname|lname|department|
|----|-----|-----|----------|
| | | | |
| | | | |
| | | | |
| | | | |
+---------------------------+
Table statistics
+----------+----+-----+-----+----------+
|otherstuff|user|fname|lname|department|
|----------|----|-----|-----|----------|
| | | | | |
| | | | | |
| | | | | |
| | | | | |
+--------------------------------------+
When statistics
gets updated daily, sometimes it has rows where fname
, lname
, and department
are NULL
due to departments
only being updated weekly. So I created a stored procedure to be called when the departments
table is updated to fix any NULL
values in statistics
.
Stored Procedure:
DELIMITER $$
CREATE PROCEDURE `update_statistics_users`()
BEGIN
#declare variables
DECLARE stat_ntid VARCHAR(10);
DECLARE done INT DEFAULT 0;
DECLARE fName, lName, dept VARCHAR(100);
#declare cursor
DECLARE cur1 CURSOR FOR
SELECT DISTINCT(user)
FROM statistics
WHERE fname IS NULL;
#declare handle
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
#open cursor
OPEN cur1;
the_loop: LOOP
#get the values of each column into variables
FETCH cur1 INTO stat_ntid;
IF done=1 THEN
LEAVE the_loop;
END IF;
#select user info into variables
SELECT first_name, last_name, department
INTO fName, lName, dept
FROM departments
WHERE ntid=stat_ntid
ORDER BY date DESC
LIMIT 1;
UPDATE statistics
SET
fname=fName, lname=lName, department=dept
WHERE user=stat_ntid AND first_name IS NULL;
END LOOP the_loop;
CLOSE cur1;
SET done = 0;
END
So the point behind it is to only return one of each user
name that has a NULL
first/last name into the cursor. Each iteration will find that username (or ntid
) in departments
and update all rows with that user
name in statistics
with the first, last, and department found.
My results are that it updates all rows with the first user
name found, then stops with a Success.
I tried finding others with this issue, but every answer I have found so far I have tried with the same results.
Aucun commentaire:
Enregistrer un commentaire