MySQL Forums
Forum List  »  German

Re: Datensätze mit bestimmten Schema löschen
Posted by: Joerg HT
Date: February 09, 2010 03:27AM

Könnte das gehen? (ist nur eine ungetestete theorie)...
Der Hintergedanke: 3 Cursor.
Cursor 1 = Zeile 1
Cursor 2 = 1 Zeile voreilend
Cursor 3 = 2 Zeilen voreilend
...wenn cursor 3 selben 'value' hat, kann primkey (zeit) von cursor 2 zum löschen gemerkt werden in einer temporären tabelle!
ob das so geht? bzw. vernünftig ist?????????????????????

CREATE PROCEDURE test()
BEGIN
DECLARE finished INT DEFAULT 0;
DECLARE z2 INT(10);
DECLARE v1, v2, v3 FLOAT;

DROP TABLE IF EXISTS tForDeletes;
CREATE TEMPORARY TABLE tForDeletes ( zeit INT(10) NOT NULL );

DECLARE CURSOR cur1 FROM SELECT value FROM tableX ORDER BY zeit ASC;
DECLARE CURSOR cur2 FROM SELECT zeit, value FROM tableX ORDER BY zeit ASC;
DECLARE CURSOR cur3 FROM SELECT value FROM tableX ORDER BY zeit ASC;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET finished = 1;

OPEN cur1;
OPEN cur2;
OPEN cur3;
FETCH cur2 INTO z2, v2;
IF NOT finished THEN
FETCH cur3 INTO v3;
IF NOT finished THEN
FETCH cur3 INTO v3;
END IF
END IF

REPEAT
IF NOT finished THEN
FETCH cur1 INTO v1;
END IF
IF NOT finished THEN
FETCH cur2 INTO z2, v2;
IF NOT finished THEN
if v1 = v2 then
FETCH cur3 INTO v3;
IF NOT finished THEN
if v3 = v2 then
INSERT INTO tForDeletes VALUES(z2);
end if
END IF
end if
END IF;
END IF;
UNTIL finished END REPEAT;

CLOSE cur3;
CLOSE cur2;
CLOSE cur1;

DELETE FROM tableX WHERE zeit IN (SELECT zeit FROM tForDeletes);

DROP TABLE tForDeletes;

END;



Edited 4 time(s). Last edit at 02/09/2010 06:56AM by Joerg HT.

Options: ReplyQuote


Subject
Views
Written By
Posted
3014
February 08, 2010 04:16AM
Re: Datensätze mit bestimmten Schema löschen
2401
February 09, 2010 03:27AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.