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