我想为我的客户找到最近的员工并更新订单表。我尝试了一个引发错误的查询。有人可以建议我的查询出了什么问题吗?唯一的选择语句可以正常工作。但是更新看起来有些不对劲。
我有3张桌子
客户_大师
Customer_ID Customer_Name WHID Cust_Location
Cust100001 Subash WH10001 0xE6100000010C1B2E724F57172A408449F1F109685340
Cust100002 Naresh WH10002 0xE6100000010CBE30992A18152A4093AAED26F8675340
员工大师
Emp_ID Emp_name WHID Emp_Location
Emp100001 Prakash WH10001 0xE6100000010C363B527DE7172A4069C36169E0675340
Emp100002 Suresh WH10002 0xE6100000010C98C3EE3B86172A4064E597C118685340
Emp100003 Vincent WH10001 0xE6100000010CE5B8533A58172A4090DD054A0A685340
Emp100004 Paul WH10002 0xE6100000010C2EE6E786A6142A40A0A696ADF5675340
Order_Tran
在Order_ID CUST_ID EMP_ID
ORD19847 Cust100001 ?????
ORD19856 Cust100002 ?????
我在主表中既有客户的位置,也有员工的位置。现在,我想更新Order_Tran表中的Emp_ID,该表最接近Customer Cust100001的Order Table中的客户位置。
我尝试了以下显示错误的查询
Update Order_Tran Set Emp_ID=(Select Top (1) Emp_ID, Employee_Master.Emp_Location.STDistance(Customer_Master.Cust_Location) AS DistanceApart FROM Customer_Master, Employee_Master WHERE Customer_ID = 'Cust100001'
and Customer_Master.WHID = Employee_Master.WHID ORDER BY DistanceApart);
尝试在子查询中选择一个值(以停止错误),并添加一个外部where子句(以确保仅更新指定的雇员)。
UPDATE Order_Tran
SET Emp_ID=(SELECT TOP (1) Emp_ID
FROM Customer_Master, Employee_Master
WHERE Customer_ID = 'Cust100001'
AND Customer_Master.WHID = Employee_Master.WHID
ORDER BY Employee_Master.Emp_Location.STDistance(Customer_Master.Cust_Location))
WHERE Customer_ID = 'Cust100001'
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句