1

I have the code on below working and bringing back the OEMPartNumber. My problem is the OEMSubNumber. AS you can see in the table I cannot match the OEMSubNumber like i did the in sql i got working. I have to match the OEMSubNumber to OEMPartNumber then back to OEM Item.. How can i do this?

sql that is working

UPDATE a 
SET a.AMIPartNumber = c.Item 
FROM imports as a 
INNER JOIN jdsubs as b 
ON a.OEMPartNumber = b.OEMPartNumber 
INNER JOIN amipartnumbers as c 
ON b.OEMPartNumber = c.OEMItem

This is my table layouts

imports
----------------
OEMPartNumber  | AMIPartNumber
AR77530        |
AR12345        |

JDSubs
---------------------------
OEMPartNumer    | OEMSubNumber
AR65123         | AR77530
AR12345         | AR56242

AMI
---------------------------
Item            | OEMItem   | Description
AMAR65123       | AR65123   | Axle
AMAR56242       | AR12345   | Spindle

UPDATE

I got this working great!

UPDATE imports
SET imports.AMIPartNumber = coalesce(apn.Item,asn.Item)
FROM imports as I 
LEFT JOIN jdsubs as PN          ON I.OEMPartNumber = PN.OEMPartNumer 
LEFT JOIN amipartnumbers as APN ON PN.OEMPartNumer = APN.OEMItem 
LEFT JOIN jdsubs as SN          ON I.OEMPartNumber = SN.OEMSubNumber 
LEFT JOIN amipartnumbers as ASN ON SN.OEMPartNumer = ASN.OEMItem
;

select
*
from imports
BigDX
  • 3,519
  • 5
  • 38
  • 52
  • 4
    Your update syntax is wrong, that's all. SQL Server's update via join does not work the same way as Access. – Aaron Bertrand May 17 '13 at 17:36
  • How do i make it right? – BigDX May 17 '13 at 17:40
  • 1
    Did you look at the duplicate? – Aaron Bertrand May 17 '13 at 17:40
  • i was trying another approach and think i got it. how does this look? UPDATE a SET a.AMIPartNumber =b.OEMPartNumber FROM imports as a INNER JOIN jdsubs as b ON a.OEMPartNumber = b.OEMPartNumber INNER JOIN amipartnumbers as c ON b.OEMPartNumber = a.OEMPartNumber – BigDX May 17 '13 at 17:49
  • i tried this but it gives me he oem number and not the ami number UPDATE a SET a.AMIPartNumber =c.Item FROM imports as a INNER JOIN jdsubs as b ON a.OEMPartNumber = b.OEMPartNumber INNER JOIN amipartnumbers as c ON a.OEMPartNumber = c.Item – BigDX May 17 '13 at 18:11
  • Sorry, but this is not a duplicate question. This is unique. I have the code on next comment working and bringing back the OEMPartNumber. My problem is the OEMSubNumber. AS you can see in the table I cannot match the OEMSubNumber like i did the in sql i got working. I have to match the OEMSubNumber to OEMPartNumber then back to OEM Item.. How can i do this? – BigDX May 17 '13 at 18:40
  • UPDATE a SET a.AMIPartNumber = c.Item FROM imports as a INNER JOIN jdsubs as b ON a.OEMPartNumber = b.OEMPartNumber INNER JOIN amipartnumbers as c ON b.OEMPartNumber = c.OEMItem – BigDX May 17 '13 at 18:41
  • 5
    This is not **unique** just because the linked duplicate doesn't answer your question without any effort on your part to read and understand the answer. You may have to read and comprehend SQL to get there but it will be worth it in the long run. – JNK May 17 '13 at 18:44
  • Ok, i understand. Sorry for posting a duplicate and ill do more research. I just thought i could ask and someone could easily answer. – BigDX May 17 '13 at 18:50
  • 1
    @AddieAnd-MicaraBaker you've been pointed to a solution that if read and considered careully will solve your problem. It may not be the exact code that you are running but apply the same concepts and you'll be fine. – Zane May 17 '13 at 19:02
  • k. thanks Zane. I looked at it before but ill look at again. – BigDX May 17 '13 at 19:06
  • i got it done with this query, but it take a lot longer with the OR statements. UPDATE a SET a.AMIPartNumber = c.Item FROM imports as a INNER JOIN jdsubs as b ON a.OEMPartNumber = b.OEMsubnumber OR a.OEMPartNumber = b.OEMPartNumber INNER JOIN amipartnumbers as c ON b.OEMPartNumber = c.OEMItem OR b.OEMsubnumber = c.OEMItem – BigDX May 17 '13 at 19:30
  • Posted my solution in the original question. There would have been no way i could have derived at that from the "duplicate" question provided – BigDX May 18 '13 at 05:09

0 Answers0