CODEWITHSUNDEEP
oracle-databasejoinsql-update
user1628169
user1628169

Reputation: 37

Oracle update with subquery alias

I have two tables:

**PRODUCTS**:  
*PART   ITEM*  
M1      A1  
M1      A2  
M1      A3  
M2      B1  
M2      B2  
M3      C1  
M3      C2  
...  

**PARTS**:
*PART   CODE*
M1      XYZ
M2      XYZ
M3      ABC
A1      XYZ
A2      MNO
A3      <null>
B1      <null>
B2      <null>
C1      <null>
...

Basically, I want to update the nulls in the PARTS table by
- taking one of the existing codes from PARTS.CODE
- where PART.PART = PRODUCT.PART
- matching PARTS.PART on PRODUCT.ITEM

What I have so far is:

update PARTS t2 
set t2.CODE =
  (
    select tx.CODE, t1.ITEM
    from PARTS tx
    join PRODUCTS t1
      on  tx.PART = t1.PART
  ) a
where t2.PART = a.ITEM
  and t2.CODE is null

The inner select brings up the ITEM and CODE that I need - at least it looks like it will match in the 'where' below that. The error I'm getting is:
Error: ORA-00933: SQL command not properly ended

Thanks much...

Upvotes: 1

Views: 7345

Answers (1)

Multisync
Multisync

Reputation: 8797

update PARTS t2 
set t2.CODE = (select max(tx.CODE) 
                 from PARTS tx join PRODUCTS t1 on tx.PART = t1.part
                where t2.part = t1.item
              )
where t2.CODE is null
  and exists(select 1 from PRODUCTS t1 where t1.item = t2.part and t2.part = t1.part);

Upvotes: 3

Related Questions