This discussion is archived
1 2 Previous Next 23 Replies Latest reply: Nov 23, 2012 2:36 AM by Manik Go to original post RSS
  • 15. Re: Help with UPDATE statment
    AlbertoFaenza Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    @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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    @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

Legend

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