Edu Edu - 6 months ago 9
SQL Question

MySQL query results are incorrect

im trying to update a table using this query:

DELIMITER $$

CREATE PROCEDURE updateDataSetHasChildren()
BEGIN
DECLARE data_set_id INT;
DECLARE done INT DEFAULT FALSE;
DECLARE result INT DEFAULT FALSE;

DECLARE data_set_cursor CURSOR FOR
SELECT id_data_set FROM data_set;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN data_set_cursor;

myloop: LOOP
-- Perform the first fetch.
FETCH data_set_cursor into data_set_id;

IF done THEN
LEAVE myloop;
END IF;

IF (SELECT COUNT(*) FROM data_sub_set WHERE id_data_set = data_set_id)>0 THEN
UPDATE data_set
SET has_children = TRUE
WHERE id_data_set = data_set_id;
ELSE
UPDATE data_set
SET has_children = FALSE
WHERE id_data_set = data_set_id;
END IF;

FETCH data_set_cursor into data_set_id;
END LOOP myloop;
CLOSE data_set_cursor;

END$$

DELIMITER ;


The problem is that when i run it it fills the column of the table with 1 0 1 0 1 0...and when i check the values are incorrect, what am i missing here?

Regards,

Answer

Remove second

FETCH  data_set_cursor into data_set_id;

at the end of the myloop. Now it is working by steps:

  1. Fetch id.
  2. Process row.
  3. Fetch id (loop end).
  4. Fetch id (second loop iteration started).
  5. Process row.
  6. Fetch id.
  7. Fetch id and so on...

Fetch is performed twice, and you are really processing every second row.

Comments