update table with values from 2 other tables

Question

I would like to find the nearest Employee for my Customer and update in order table. I tried a Query which throws an error. Can any one suggest what am doing wrong on my query? The only select Statement is working fine. But the update looks some thing wrong.

I have 3 tables

Customer_Master

 Customer_ID Customer_Name WHID Cust_Location Cust100001 Subash WH10001 0xE6100000010C1B2E724F57172A408449F1F109685340 Cust100002 Naresh WH10002 0xE6100000010CBE30992A18152A4093AAED26F8675340

Employee_Master

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 ?????

I have Location of the customer and also Employee in Master Tables. Now i want to update Emp_ID in Order_Tran table who is nearest to the customer location in Order Table for Customer Cust100001.

I tried the below query which is showing error

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

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. — Subash
– Subash, Commented Oct 4, 2013 at 15:16
your update statement is trying to set Emp_ID equal to 2 separate fields, the select sub query should only select one field. Also, that query will update every row in your Order_Tran table, since there is no outer WHERE statement. — RobJohnson
– RobJohnson, Commented Oct 4, 2013 at 15:23
Hi Rob, What you said is absolutely correct. But i need to have two fields in my select to obtain the relevant value. Is there any way i can get the output of subquery as a single field? — Subash
– Subash, Commented Oct 4, 2013 at 15:27

RobJohnson · Accepted Answer · 2013-10-04 15:32:30Z

Try selecting a single value in your sub-query (to stop the error) and adding an outer where clause (to ensure only the specified employee is updated).

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'

Collectives™ on Stack Overflow

update table with values from 2 other tables

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related