September 24, 2003 at 12:59 pm
I have a problem with a join of 3 tables.
The tables are described below
Master (table name) alias MASTER
OrderID
Port_Desc
Access (table name) alias ACCESS
OrderID
Post_CD
Port_Type (table name) alias SPEED
Post_CD
Port_Desc
I am trying to update master (field PORT_DESC) using the below code. Needless to say it does not work.
update master
set Port_Desc = SPEED. Port_Desc
from Port_Type SPEED
left outer JOIN ACCESS ACCESS on ACCESS. Post_CD = SPEED. Post_CD
where SPEED. Post_CD > 0
left outer JOIN master MASTER on ACCESS.ORDER_ID = MASTER.ORDER_ID
I am not very good at joining tables but I thought I did this right. Any help you can offer may help save some hair!
September 24, 2003 at 1:34 pm
Try something like this...
UPDATE M
SET Port_Desc = S.Port_Desc
FROM master M
JOIN ACCESS A ON M.OrderID = A.OrderID
JOIN Port_Type S ON A.Post_CD = S.Post_CD AND S.Post_CD > 0
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
September 24, 2003 at 1:35 pm
Your WHERE clause comes after all of the JOIN clauses:
UPDATE master
SET Port_Desc = SPEED.Port_Desc
FROM Port_Type SPEED
LEFT OUTER JOIN ACCESS ACCESS on
ACCESS.Post_CD = SPEED. Post_CD
LEFT OUTER JOIN master MASTER on
ACCESS.ORDER_ID = MASTER.ORDER_ID
WHERE SPEED.Post_CD > 0
quote:
I have a problem with a join of 3 tables.The tables are described below
Master (table name) alias MASTER
OrderID
Port_Desc
Access (table name) alias ACCESS
OrderID
Post_CD
Port_Type (table name) alias SPEED
Post_CD
Port_Desc
I am trying to update master (field PORT_DESC) using the below code. Needless to say it does not work.
update master
set Port_Desc = SPEED. Port_Desc
from Port_Type SPEED
left outer JOIN ACCESS ACCESS on ACCESS. Post_CD = SPEED. Post_CD
where SPEED. Post_CD > 0
left outer JOIN master MASTER on ACCESS.ORDER_ID = MASTER.ORDER_ID
I am not very good at joining tables but I thought I did this right. Any help you can offer may help save some hair!
September 24, 2003 at 1:47 pm
Also note that in my query I didn't use an outer join as it looks to me like you only want to update the records in Master that are pertinent to the Access and Port_Type data. If you were to use left joins you may end up changing data you don't want to change. I also moved the where clause to the join on the Port_Type as that will help performance just slightly. Using INNER JOINS instead of OUTER JOINS will significantly improve performance as well.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
September 24, 2003 at 2:27 pm
thank you! GLLJR, your solution made everything easier for me. I need to get a good t-sql book that has some good examples. I seem to learn by example more than creative juices!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply