UPDATE table by looping through rows

[dwayned]

Hi Guys,

I have imported an excel document into MySQL to tidy up and make better use of the data but I am having problems running an update.

All tables used as examples below;

Tables
asset_register
phone
sim
team

The asset_register contains the following cols;

id INT,
serialNo VARCHAR,
simNo INT,
team VARCHAR

The problem is that I want to create a team table and the reference the teamId in the asset_register table.

Does that make sense? Instead of storing the actual team name i want a foreign key to the teams table.

The problem lies in updating all the data in the asset_register table which was imported from excel. There are nearly the same no of teams as users (~500) and I need to write a query or Stored Procedure to update all of them.

I thought that a stored procedure would be the way to go. So far i have the following but it updated the value of team to 'NULL' instead of the teamId.

DELIMITER //
DROP PROCEDURE `updateCrews`//
CREATE DEFINER=`root`@`localhost` PROCEDURE `updateCrews`()

BEGIN
	
	DECLARE rowCount INT;
	DECLARE crewNameFromRegister VARCHAR(7);
	DECLARE currentRow INT;
	DECLARE newCrewdata VARCHAR(7);
	
	SET rowCount=(SELECT COUNT(*) FROM asset_register);
	SET currentRow = 1;
	
	myUpdateLoop: WHILE (currentRow < rowCount) DO
	
		SET crewNameFromRegister = (SELECT crewID FROM asset_register WHERE id = currentRow);	
		SET newCrewData = (SELECT id FROM crews WHERE crewName = crewNameFromRegister);
		UPDATE asset_register SET crewID = newCrewData;
		
		SET currentRow = currentRow + 1;
		
	END WHILE myUpdateLoop;
	
END//

DELIMITER ;

Any help would be greatly appreciated, there is probably a better way to do this and a nudge in the right direction would be great.

thanks
Dwayne

[dwayned]

Question answered on another forum.
I created a new col crew_id and then ran the following query

UPDATE asset_register, crews
SET    asset_register.crewID = crews.id
WHERE  crews.crewName = asset_register.crewID

See link below
http://stackoverflow.com/questions/7529893/update-multiple-rows-to-value-from-another-table-fk