4puMJnRS

1DELIMITER //
2CREATE PROCEDURE MoveTeamsProc ()
3	BEGIN
4		#DECLARE OldRowsCount001, OldRowsCount002, NewRowsCount001, NewRowsCount002 INT DEFAULT 0;
5		#START TRANSACTION;
6		#SET OldRowsCount001 = (SELECT Count(ClubID) FROM ClubStatus WHERE DivisionID = "001");
7        CREATE TEMPORARY TABLE IF NOT EXISTS Temp AS (SELECT * FROM ClubStatus);
8		UPDATE Club
9			SET DivisionID = "001" 
10            WHERE TotalPoints IN (SELECT * FROM Temp WHERE DivisionID = "002" ORDER BY TotalPoints DESC LIMIT 2);
11		#SET NewRowsCount001 = (SELECT Count(ClubID) FROM ClubStatus WHERE DivisionID = "001");
12       # SET OldRowsCount002 = (SELECT Count(ClubID) FROM ClubStatus WHERE DivisionID = "002");
13		UPDATE Club
14			SET DivisionID = "002"
15            WHERE Club.ClubID IN (SELECT * FROM Temp WHERE DivisionID = "001" ORDER BY TotalPoints LIMIT 2);
16		#SET NewRowsCount002 = (SELECT Count(ClubID) FROM ClubStatus WHERE DivisionID = "002");
17       # IF (OldRowsCount001 = NewRowsCount001+2) AND (OldRowsCount002 = NewRowsCount002-2)
18		#	THEN COMMIT;
19          #  ELSE ROLLBACK;
20		#END IF;
21	END; //
22DELIMITER ;