This discussion is archived
2 Replies Latest reply: Jan 25, 2013 3:34 AM by 972756 RSS

Help in Update Statement

987124 Newbie
Currently Being Moderated
I Have 3 Tables Invoice,MastData and Orders.

Where Invoice Table contains Jobno, Challanno
MastData Table contains Board_Name,Filenum
Orders Table contains Filenum, Ofnumber

Now i want to update Invoice.Challanno with Orders.ofnumber where date falls in year 2013 and invoice.jobno starting with 'D'
I am new to Oracle came from MySql / MSSql and executing following sql.

update (select a.challanno, c.ofnumber
from Invoice a
join mastdata b on A.JOBNO=B.BOARD_NAME
join orders c on C.FILENUM=B.FILENUM
where to_char(a.docdate,'yyyymmdd') > '20121231' and substr(a.jobno,1,1)='D')
set challanno=ofnumber

Getting error
"ORA-01779: cannot modify a column which maps to a non key-preserved table"

Anybody can help what is wrong ?

Edited by: 984121 on Jan 25, 2013 3:14 AM
  • 1. Re: Help in Update Statement
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Welcome to the forum!

    You can use MERGE for that; depending on you data and your requirements, something like this:
    MERGE INTO  invoice     dst
    USING (
              SELECT  m.board_name
           ,       o.ofnumber
           FROM       mastdata     m
           JOIN       orders     o  ON  o.filenum  = m.filenum
           WHERE       m.board_name     LIKE 'D%'
          )               src
    ON    (src.board_name     = dst.jobno)
    WHEN MATCHED THEN UPDATE
    SET   dst.challanno     = src,ofnumber
    WHERE dst.docdate     >= DATE '2013-01-01'
    ;
    This assumes that mastdata.boardname is unique after joining with orders.


     

    I hope this answers your question.
    If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.
    In the case of a DML operation (such as UPDATE) the sample data should show what the tables are like before the DML, and the results will be the contents of the changed table(s) after the DML.
    Explain, using specific examples, how you get those results from that data.
    Always say what version of Oracle you're using (e.g. 11.2.0.2.0).
    See the forum FAQ {message:id=9360002}
  • 2. Re: Help in Update Statement
    972756 Newbie
    Currently Being Moderated
    B.BOARD_NAME and C.FILENUM have to be Primary Keys, or at least have one Unique index on them.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points