locked
update using two table in oracle RRS feed

  • Question

  • User64117442 posted

    Hi All,

    i want to update through two table

    my code is 

     UPDATE PROD_DESC
        SET STATUS = 'N'
        FROM  PRO P   
        INNER JOIN PROD_DESC CD
        WHERE P.ID=CD.P_ID AND P.ID  = 1  AND ISTEMP='Y';
        

    it is showing error

    ORA-00933: SQL command not properly ended
    
    
    whats wrong with it ?
    Thanks in Advance
    Wednesday, January 11, 2012 1:50 AM

Answers

  • User551462331 posted

    I dont think inner join in update is possible in oracle as we do sql server

    but, simillar approch of using subquery should work

     UPDATE PROD_DESC prod
        SET STATUS = 
        (
          select 'N' 
          FROM  PRO P   
          WHERE P.ID = prod.P_ID AND P.ID  = 1
        )
    where ISTEMP='Y';

    note that table PROD_Desc is used in where condtion of sub query...

    hope this helps...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 11, 2012 2:47 AM

All replies

  • User551462331 posted

    try this

     UPDATE PROD_DESC prod
     SET STATUS = 'N' 
    where prod.P_ID in (select ID from PRO where ID = 1 and IsTemp='Y')
        

    if IsTemp belongs to table PROD_DEsC then move that condition out

    hope this helps...

    Wednesday, January 11, 2012 2:25 AM
  • User64117442 posted

    hi,

    i don`t want to use IN statement .

    i want to accompalish it using join

    Wednesday, January 11, 2012 2:32 AM
  • User551462331 posted

    I dont think inner join in update is possible in oracle as we do sql server

    but, simillar approch of using subquery should work

     UPDATE PROD_DESC prod
        SET STATUS = 
        (
          select 'N' 
          FROM  PRO P   
          WHERE P.ID = prod.P_ID AND P.ID  = 1
        )
    where ISTEMP='Y';

    note that table PROD_Desc is used in where condtion of sub query...

    hope this helps...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 11, 2012 2:47 AM