我已将 Excel 文档导入 MySQL 以整理并更好地利用数据，但运行更新时遇到问题。
以下所有表格均用作示例；
表格
资产注册
电话
模拟
team
asset_register 包含以下列；
id INT,
serialNo VARCHAR,
simNo INT,
team VARCHAR
问题是我想创建一个团队表并引用 asset_register 表中的 teamId 。
这有道理吗？我不想存储实际的团队名称，而是想要团队表的外键。
问题在于更新从 Excel 导入的 asset_register 表中的所有数据。团队数量与用户几乎相同（~500），我需要编写一个查询或存储过程来更新所有团队。
我认为存储过程是可行的方法。到目前为止，我有以下内容，但它将 team 的值更新为“NULL”而不是 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 ;
任何帮助将不胜感激，可能有更好的方法来做到这一点，并且朝着正确的方向推动会很棒。
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.
发布评论
评论（1）
如果我很好地理解你的问题，我建议你使用本文中mysql手册中描述的多表更新语法：MutliTableUpdate。您尝试通过 id 更改字符串键，请注意数据类型/转换。
想法应该是：
但是如果您使用其他列列来执行此操作并检查中间结果，可能会更安全。
在第一个示例中：
您可以添加 teamID 列，进行更新并重命名您的列
If I good understand your question I suggest you to use a mutli table update syntax describe in mysql manual in this article : MutliTableUpdate. You try to change a string key by an id, be carreful of data type / conversion.
The idea should be:
But it could be safer if you use an other column column to do it and check the intermediate result.
On your first example:
you can add a teamID column, do the update and rename your column