This discussion is archived
1 2 Previous Next 23 Replies Latest reply: Nov 23, 2012 2:36 AM by Manik RSS

Help with UPDATE statment

923195 Newbie
Currently Being Moderated
Hello,

i have a table like this:
create table test
(id number,
stat number,
id_num number);
data in table:
insert into table test (id,stat,id_num) values (1,112,'');
insert into table test (id,stat,id_num) values (1,123,'');
insert into table test (id,stat,id_num) values (2,134,'');
insert into table test (id,stat,id_num) values (2,111,'');
insert into table test (id,stat,id_num) values (3,112,'');
insert into table test (id,stat,id_num) values (4,111,'');
insert into table test (id,stat,id_num) values (4,12,'');
insert into table test (id,stat,id_num) values (4,11,'');
I want to update column id_num with sequence number under same ID.
Below is the table with correctly updated coloumn.
insert into table test (id,stat,id_num) values (1,112,1);
insert into table test (id,stat,id_num) values (1,123,2);
insert into table test (id,stat,id_num) values (2,134,1);
insert into table test (id,stat,id_num) values (2,111,2);
insert into table test (id,stat,id_num) values (3,112,1);
insert into table test (id,stat,id_num) values (4,111,1);
insert into table test (id,stat,id_num) values (4,12,2);
insert into table test (id,stat,id_num) values (4,11,3);
Can someone give me a hint how to pull this off?

Thank you for your help!
  • 1. Re: Help with UPDATE statment
    bencol Pro
    Currently Being Moderated
    hint:
    row_number()
  • 2. Re: Help with UPDATE statment
    Manik Expert
    Currently Being Moderated
    MERGE INTO test tgt
         USING (SELECT a.id,
                       a.stat,
                       ROW_NUMBER () OVER (PARTITION BY id ORDER BY 1) id_num
                  FROM test a) src
            ON (tgt.id = src.id AND tgt.stat = src.stat)
    WHEN MATCHED THEN
       UPDATE SET tgt.id_num = src.id_num;
    
    Commit;
    Cheers,
    Manik.
  • 3. Re: Help with UPDATE statment
    923195 Newbie
    Currently Being Moderated
    Yea i tried that problem is i need to set numbers lets say from 1 -3 inside each ID.

    Check out my send insert block how it should be done.

    Thank you.
  • 4. Re: Help with UPDATE statment
    bencol Pro
    Currently Being Moderated
    That is what row_number() will do if you partition by id and order by stat. What if you have more that 3 ids? mod(row_number)?
  • 5. Re: Help with UPDATE statment
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi,

    what is the order of assigning the sequence within the same id?

    You cannot rely on the order of row number as Oracle does not ensure that it is giving back the rows in the sequence in which they have been inserted.

    I have assumed here that within the id the id_num is numbered ordered by stat:
    MERGE INTO test a
         USING (SELECT id, stat
                     , ROW_NUMBER () OVER (PARTITION BY id ORDER BY stat) AS val
                  FROM test) b
            ON (a.id = b.id AND a.stat = b.stat)
    WHEN MATCHED
    THEN
       UPDATE SET id_num = val;
    
    SELECT *
      FROM test
     ORDER BY id, stat;
    
            ID       STAT     ID_NUM
    ---------- ---------- ----------
             1        112          1
             1        123          2
             2        111          1
             2        134          2
             3        112          1
             4         11          1
             4         12          2
             4        111          3
    Regards.
    Al
  • 6. Re: Help with UPDATE statment
    923195 Newbie
    Currently Being Moderated
    I said 3 IDs for example ... if i have 100 same IDs in table then the numbers in ID_NUM should be from 1 - 100.
  • 7. Re: Help with UPDATE statment
    AlbertoFaenza Expert
    Currently Being Moderated
    user13071990 wrote:
    I said 3 IDs for example ... if i have 100 same IDs in table then the numbers in ID_NUM should be from 1 - 100.
    The problem is not the number but which is the order you want to assign.
    If you use
     ROW_NUMBER () OVER (PARTITION BY id ORDER BY stat) AS val
    as I mentioned in my statement you will get id_num within the same id in order of stat column.

    If you set it like this:
    ROW_NUMBER () OVER (PARTITION BY id ORDER BY 1) AS val
    Then you might get order as you insert your record but this is not ensured by Oracle.
    Ideally you should decide in which order you want to assign id_num and possibly use an existing column.

    Regards.
    Al
  • 8. Re: Help with UPDATE statment
    923195 Newbie
    Currently Being Moderated
    @Alberto Faenza: You are correct ! Really thank you for this nice example.

    But i have a follow up question ... can it be done without using stat column? So only with column id      ?

    PS order by is not a problem because in my original table i am sorting it by date.

    Edited by: user13071990 on Nov 23, 2012 1:59 AM
  • 9. Re: Help with UPDATE statment
    AlbertoFaenza Expert
    Currently Being Moderated
    user13071990 wrote:
    @Alberto Faenza: You are correct ! Really thank you for this nice example.

    But i have a follow up question ... can it be done without using stat column? So only with column id      ?

    PS order by is not a problem because in my original table i am sorting it by date.

    Edited by: user13071990 on Nov 23, 2012 1:59 AM
    That's why is important to post all relevant data.

    Assuming that you want to assign id sequence order by date (i.e. id_date if you have a column like this) you can do what follows:
    MERGE INTO test a
         USING (SELECT id, stat
                     , ROW_NUMBER () OVER (PARTITION BY id ORDER BY id_date) AS val
                  FROM test) b
            ON (a.id = b.id AND a.stat = b.stat)
    WHEN MATCHED
    THEN
       UPDATE SET id_num = val;
    Within the same id, id_num will be numbered in order of id_date.

    Edit:
    Important: Also another assumption is that your combination id, stat is unique within your file.

    Regards.
    Al

    Edited by: Alberto Faenza on Nov 23, 2012 11:02 AM
  • 10. Re: Help with UPDATE statment
    Manik Expert
    Currently Being Moderated
    Did you check my solution already posted...????

    Cheers,
    Manik.
  • 11. Re: Help with UPDATE statment
    ranit B Expert
    Currently Being Moderated
    user13071990 wrote:
    @Alberto Faenza: You are correct ! Really thank you for this nice example.

    But i have a follow up question ... can it be done without using stat column? So only with column id      ?

    PS order by is not a problem because in my original table i am sorting it by date.

    Edited by: user13071990 on Nov 23, 2012 1:59 AM
    No Andy. that's not possible along with row_number.

    But if you are not concerned abt the ordering, let the ORDER BY be as it is.. right ?
  • 12. Re: Help with UPDATE statment
    ranit B Expert
    Currently Being Moderated
    Manik wrote:
    Did you check my solution already posted...????

    Cheers,
    Manik.
    This works but same result is not guaranteed always.
    Nicely explained by Al.
  • 13. Re: Help with UPDATE statment
    923195 Newbie
    Currently Being Moderated
    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.
  • 14. Re: Help with UPDATE statment
    Manik Expert
    Currently Being Moderated
    Run this:
    SELECT a.*, ROW_NUMBER () OVER (PARTITION BY id ORDER BY dat_document)
      FROM test a;
    If you find it satisfactory, replace this in src query of my merge statement.

    Cheers,
    Manik.
1 2 Previous Next

Legend

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