Reputation: 83
Where is the syntax error?
DECLARE irid INT DEFAULT 0;
DECLARE tmp_joinid INT DEFAULT 0;
DECLARE loopjoins_eof INT DEFAULT FALSE;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET loopjoins_eof = TRUE;
START TRANSACTION;
SET irid = (SELECT id FROM `tables` WHERE `adapter_id`=_aid AND `view_id`=_vid AND `name`=_tname);
IF irid IS NOT NULL THEN
DECLARE cur0 CURSOR FOR SELECT `joins`.`id` FROM `joins` WHERE `table_left_id`=irid OR `table_right_id`=irid;
OPEN cur0;
loopjoins: LOOP
FETCH cur0 INTO tmp_joinid;
IF loopjoins_eof THEN
LEAVE loopjoins;
END IF;
-- Lösche Join-Columns
DELETE FROM `join_columns` WHERE `join_id`=tmp_joinid;
END LOOP loopjoins;
CLOSE cur0;
END IF;
COMMIT;
SELECT irid;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE cur0 CURSOR FOR SELECT joins
.id
FROM joins
WHERE table_left_id
=i' at line 12
Thank you
Upvotes: 1
Views: 2018
Reputation: 6146
a much better option is to avoid the cursor, you can replace the cursor with something like
DELETE FROM `join_columns`
WHERE `join_id` in
(SELECT `id`
FROM `joins`
WHERE `table_left_id`=irid OR `table_right_id`=irid);
Upvotes: 3
Reputation: 51948
From the manual:
Cursor declarations must appear before handler declarations and after variable and condition declarations.
Upvotes: 2