3 Replies Latest reply: Nov 24, 2012 11:15 AM by Ayham RSS

    Update column by max-no where it has NULL value

    Ayham
      Hi All,

      I have the following table and date i want to update column that has NULL values with
      different date, These data is the maxmium number in same column.
      create table test (id number(9), name number(9));
      
      insert into test values(1,2);
      insert into test values(2,2);
      insert into test values(3,2);
      insert into test values(4,2);
      insert into test values(5,2);
      insert into test values(6,null);
      insert into test values(7,null);
      insert into test values(7,null);
      I have used this but this update the columns with same value . So that is not what i want
      update test set name=(select max(name)+1) from test) where name is null;
      i want the output like this
      id     name
      1       2
      2       2
      3       2
      4       2
      5       2
      6       3
      7       4
      7       5
      Note: the Id column maybe duplicate
      many thanks in advance

      Edited by: Ayham on Nov 23, 2012 11:51 PM
        • 1. Re: Update column by max-no where it has NULL value
          Stew Ashton
          LOCK TABLE TEST IN EXCLUSIVE MODE;
          
          MERGE INTO TEST O
          using (
            SELECT T.ROWID RID,
            ROW_NUMBER() OVER(ORDER BY T.ID, T.ROWID) + MAX_NAME NEW_NAME
            FROM TEST T, (SELECT MAX(NAME) MAX_NAME FROM TEST)
            WHERE T.NAME IS NULL
          ) N
          ON (O.ROWID = N.RID)
          when matched then update set o.name = n.new_name;
          
          commit;
          You have to lock the table before using this command, otherwise somebody else could update one of the rows during the MERGE and the results would not be what you want.

          Be sure to COMMIT right away to unlock the table.
          • 2. Re: Update column by max-no where it has NULL value
            Solomon Yakobson
            SQL> select  *
              2    from  test
              3  /
            
                    ID       NAME
            ---------- ----------
                     1          2
                     2          2
                     3          2
                     4          2
                     5          2
                     6
                     7
                     7
            
            8 rows selected.
            
            SQL> update  test t1
              2     set  name = (
              3                  select  name
              4                    from  (
              5                           select  rowid rid,
              6                                   max(name) over() + row_number() over(order by name nulls first,id,rowid) name
              7                             from  test
              8                          ) t2
              9                    where t2.rid = t1.rowid
             10                 )
             11    where name is null
             12  /
            
            3 rows updated.
            
            SQL> select  *
              2    from  test
              3  /
            
                    ID       NAME
            ---------- ----------
                     1          2
                     2          2
                     3          2
                     4          2
                     5          2
                     6          3
                     7          4
                     7          5
            
            8 rows selected.
            
            SQL> 
            SY.
            • 3. Re: Update column by max-no where it has NULL value
              Ayham
              Many thanks for you.