CODEWITHSUNDEEP
sql-server
Subash
Subash

Reputation: 33

update table with values from 2 other tables

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

Upvotes: 0

Views: 47

Answers (1)

RobJohnson
RobJohnson

Reputation: 885

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'

Upvotes: 1

Related Questions