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;
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.