1 2 Previous Next 23 Replies Latest reply: Nov 23, 2012 4:49 AM by 923195 Go to original post RSS
      • 15. Re: Help with UPDATE statment
        AlbertoFaenza
        user13071990 wrote:
        Let me repost:


        table:
        create table test
        (id number,
        dat_document,
        id_num number);
        Data in table:
        insert into table test (id,dat_document,id_num) values (1,to_date('01.01.2012','dd.mm.yyyy'),'');
        insert into table test (id,dat_document,id_num) values (1,to_date('01.02.2012','dd.mm.yyyy'),'');
        insert into table test (id,dat_document,id_num) values (2,to_date('01.03.2012','dd.mm.yyyy'),'');
        insert into table test (id,dat_document,id_num) values (2,to_date('01.04.2012','dd.mm.yyyy'),'');
        insert into table test (id,dat_document,id_num) values (3,to_date('01.05.2012','dd.mm.yyyy'),'');
        insert into table test (id,dat_document,id_num) values (4,to_date('01.06.2012','dd.mm.yyyy'),'');
        insert into table test (id,dat_document,id_num) values (4,to_date('01.07.2012','dd.mm.yyyy'),'');
        insert into table test (id,dat_document,id_num) values (4,to_date('01.08.2012','dd.mm.yyyy'),'');
        My original question is answered i am just wondering how can this be done with using only ID and dat_document for sort?

        @Manik: I am sorry i totally ower looked your answer. I will credit you as well for it.
        Please,

        when you post statemens be sure they are valid.

        You can do in this way with the assumption that you have unique records for the same id, dat_document
        MERGE INTO test a
             USING (SELECT id, dat_document
                         , ROW_NUMBER () OVER (PARTITION BY id ORDER BY dat_document) AS val
                      FROM test) b
                ON (a.id = b.id AND a.dat_document = b.dat_document)
        WHEN MATCHED
        THEN
           UPDATE SET id_num = val;
        
        SELECT *
          FROM test
        ORDER BY id, dat_document;
        
                ID DAT_DOCUMENT              ID_NUM
        ---------- --------------------- ----------
                 1 01-01-2012 00:00:00            1
                 1 01-02-2012 00:00:00            2
                 2 01-03-2012 00:00:00            1
                 2 01-04-2012 00:00:00            2
                 3 01-05-2012 00:00:00            1
                 4 01-06-2012 00:00:00            1
                 4 01-07-2012 00:00:00            2
                 4 01-08-2012 00:00:00            3
        Regards.
        Al
        • 16. Re: Help with UPDATE statment
          923195
          @You can do in this way with the assumption that you have unique records for the same id, dat_document

          I thought so ... i dont have unique values not in id and not in dat_document.

          @manik: it doesnt work ... it fails on "ON" statment. If i run select by hand i get correct values taht i want but trying to do update statment it fails with "to many values error"

          As i said the only thing right now is column ID and dat_document and those are not unique we can have lots of same IDs and dat_document values in this table.

          Is there a way to do this?

          PS
          MERGE INTO test a
               USING (SELECT id, dat_document
                           , ROW_NUMBER () OVER (PARTITION BY id ORDER BY dat_document) AS val
                        FROM test) b
                          ON (a.id = b.id AND  ??? = ???? )             --ON (a.id = b.id AND a.dat_document = b.dat_document)
          WHEN MATCHED
          THEN
             UPDATE SET id_num = val;
          Edited by: user13071990 on Nov 23, 2012 2:22 AM
          • 17. Re: Help with UPDATE statment
            Manik
            MERGE INTO test tgt
                 USING (SELECT id,
                               dat_document,
                               ROW_NUMBER () OVER (PARTITION BY id ORDER BY dat_document)
                                  id_num
                          FROM test a) src
                    ON (tgt.id = src.id AND tgt.dat_document = src.dat_document)
            WHEN MATCHED THEN
               UPDATE SET tgt.id_num = src.id_num;
            Cheers,
            Manik.
            • 18. Re: Help with UPDATE statment
              AlbertoFaenza
              user13071990 wrote:
              @You can do in this way with the assumption that you have unique records for the same id, dat_document

              I thought so ... i dont have unique values not in id and not in dat_document.

              @manik: it doesnt work ... it fails on "ON" statment. If i run select by hand i get correct values taht i want but trying to do update statment it fails with "to many values error"
              Yes, this is because with MERGE you need to find a way to identify uniquely the record to update.

              Do you have a unique key on this table?
              Posting your table structure indicating all columns and identifying if you have a unique index will help.

              Regards.
              Al

              Edited by: Alberto Faenza on Nov 23, 2012 11:29 AM
              • 19. Re: Help with UPDATE statment
                923195
                Manik that is not a correct solution since dat_document can also be duplicated.

                Alberto there are no uniq keys in table, thats the main problem.
                • 20. Re: Help with UPDATE statment
                  Manik
                  AND where did u mention that it is duplicated.. you seem like changing the requirement for every post.. :)

                  Did you bother to execute what we are posting, if YES what is your expected ouput for your latest data???

                  Cheers,
                  Manik.
                  • 21. Re: Help with UPDATE statment
                    AlbertoFaenza
                    user13071990 wrote:
                    Manik that is not a correct solution since dat_document can also be duplicated.

                    Alberto there are no uniq keys in table, thats the main problem.
                    It's not a good way to have table without unique keys.

                    As a workaround I can suggest this, despite I don't like to use ROWID pseudocolumn.

                    I have made a test inserting a duplicate date for the same id
                    insert into test (id,dat_document,id_num) values (4,to_date('01.07.2012','dd.mm.yyyy'),'');
                    Now I have used:
                    MERGE INTO test a
                         USING (SELECT rowid
                                     , ROW_NUMBER () OVER (PARTITION BY id ORDER BY dat_document) AS val
                                  FROM test) b
                            ON (a.rowid = b.rowid)
                    WHEN MATCHED
                    THEN
                       UPDATE SET id_num = val;
                    
                    SELECT *
                      FROM test
                    ORDER BY id, dat_document;
                    
                            ID DAT_DOCUMENT              ID_NUM
                    ---------- --------------------- ----------
                             1 01-01-2012 00:00:00            1
                             1 01-02-2012 00:00:00            2
                             2 01-03-2012 00:00:00            1
                             2 01-04-2012 00:00:00            2
                             3 01-05-2012 00:00:00            1
                             4 01-06-2012 00:00:00            1
                             4 01-07-2012 00:00:00            2
                             4 01-07-2012 00:00:00            3
                             4 01-08-2012 00:00:00            4
                    Regards.
                    Al
                    • 22. Re: Help with UPDATE statment
                      923195
                      No as you can see it in my create table statment there are no primary keys defined and thos no unique values can be espected in table.

                      I did execute your statments but:
                      create table test
                      (ID NUMBER,
                      dat_document date,
                      id_num number);
                      
                      INSERT INTO  TEST (ID,dat_document,id_num) VALUES (1,to_date('01.01.2012','dd.mm.yyyy'),'');
                      insert into  test (id,dat_document,id_num) values (1,to_date('01.02.2012','dd.mm.yyyy'),'');
                      insert into  test (id,dat_document,id_num) values (2,to_date('01.03.2012','dd.mm.yyyy'),'');
                      insert into  test (id,dat_document,id_num) values (2,to_date('01.04.2012','dd.mm.yyyy'),'');
                      insert into  test (id,dat_document,id_num) values (3,to_date('01.05.2012','dd.mm.yyyy'),'');
                      INSERT INTO  TEST (ID,dat_document,id_num) VALUES (4,to_date('01.06.2012','dd.mm.yyyy'),'');
                      INSERT INTO  TEST (ID,dat_document,id_num) VALUES (4,to_date('01.07.2012','dd.mm.yyyy'),'');
                      insert into  test (id,dat_document,id_num) values (4,to_date('01.07.2012','dd.mm.yyyy'),'');
                      
                      
                      MERGE INTO test tgt
                           USING (SELECT id,
                                         dat_document,
                                         ROW_NUMBER () OVER (PARTITION BY id ORDER BY dat_document)
                                            id_num
                                    FROM test a) src
                              ON (tgt.id = src.id AND tgt.dat_document = src.dat_document)
                      WHEN MATCHED THEN
                         UPDATE SET tgt.id_num = src.id_num;
                      This is the error;
                      QL Error: ORA-30926: unable to get a stable set of rows in the source tables
                      30926. 00000 -  "unable to get a stable set of rows in the source tables"
                      *Cause:    A stable set of rows could not be got because of large dml
                                 activity or a non-deterministic where clause.
                      *Action:   Remove any non-deterministic where clauses and reissue the dml.
                      • 23. Re: Help with UPDATE statment
                        923195
                        @Alberto Faenza: Correct!

                        Thank you!

                        @Maik: Thank you as well i am sorry about my non-consistent post, I know it was confusing and i should state both of the examples in my original post.


                        Thank you both for your time!
                        1 2 Previous Next