INSERT INTO Agent (agentID, agentName, locationID)
SELECT DISTINCT pantodata.agentID, pantodata.agentName FROM pantodata
CASE
WHEN pantodata.AgentTown = 'Stirling' THEN locationID = 6
WHEN pantodata.AgentTown = 'Dunblane' THEN locationID = 3
END
FROM pantodata
Pantodata table:
agentID AgentName AgentTown
100 John Stirling
101 Jack Dunblane
Location table:
locationID City
1 Bannockburn
2 Dollar
3 Dunblane
4 Falkirk
5 Menstrie
6 Stirling
Agent table:
agentID agentName locationID
我正在尝试将pantodata中的agentID和pantodata中的agentName插入到代理表中。然后，我试图检查该代理位于（从pantodata表中）哪个城镇，然后根据pantodata表中指定的来自哪个城镇，将locationID（在agent表中）分配给某个号码。
最佳答案
查询中存在一些语法错误。请尝试以下更正的版本。
INSERT INTO Agent (agentID, agentName, locationID)
SELECT DISTINCT pantodata.agentID, pantodata.agentName,
CASE
WHEN pantodata.AgentTown = 'Stirling' THEN 6
WHEN pantodata.AgentTown = 'Dunblane' THEN 3
END
FROM pantodata
使用
Location表。假定位置表中locationid和city之间存在一对一的关系。INSERT INTO Agent (agentID, agentName, locationID)
SELECT p.agentID, p.agentName, l.locationid
FROM pantodata p
JOIN Location l on p.AgentTown = l.city