2 Replies Latest reply: Jan 25, 2013 5:34 AM by 972756 RSS

    Help in Update Statement

    987124
      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
          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
            B.BOARD_NAME and C.FILENUM have to be Primary Keys, or at least have one Unique index on them.