1 2 3 Previous Next 35 Replies Latest reply: Dec 10, 2009 3:18 PM by MichaelS RSS

    Alter Table Add column not null default value

    732390
      I want to add two columns to a table with not null and default as 0 for both columns
      Can i write the whole in one statement or do i have to split statement
      I tried this, but didn't work

      alter table DWSODS01.DWT00301_ORD_DTL_OMS add (
      COMB_ORD_FLG NUMBER(5,0) default 0 not null,
      COMB_ORD_NO NUMBER(12,0)
      default 0 not null);

      How can i modify the code?
        • 1. Re: Alter Table Add column not null default value
          Beijing
          alter table DWSODS01.DWT00301_ORD_DTL_OMS add (
          COMB_ORD_FLG NUMBER(5,0) default 0 not nulll); 
          
          alter table DWSODS01.DWT00301_ORD_DTL_OMS add (
          COMB_ORD_NO NUMBER(12,0) default 0 not null); 
          • 2. Re: Alter Table Add column not null default value
            561825
            >
            I tried this, but didn't work
            >

            Very intuitive way to describe your problem. Try this
            alter table DWSODS01.DWT00301_ORD_DTL_OMS 
            add (
                        COMB_ORD_FLG NUMBER(5,0) not null default 0 , 
                        COMB_ORD_NO NUMBER(12,0) not null default 0
                   ); 
            Regards

            Raj

            P.S : Not tested
            • 3. Re: Alter Table Add column not null default value
              Solomon Yakobson
              user10390682 wrote:
              I tried this, but didn't work
              Since you are specifying default values, it should work (regardless if table DWSODS01.DWT00301_ORD_DTL_OMS is empty or not):
              SQL> select count(*) from emp1
                2  /
              
                COUNT(*)
              ----------
                      14
              
              SQL> alter table emp1 add (
                2  COMB_ORD_FLG NUMBER(5,0) default 0 not null, 
                3  COMB_ORD_NO NUMBER(12,0) 
                4  default 0 not null); 
              
              Table altered.
              
              SQL> desc emp1
               Name                                      Null?    Type
               ----------------------------------------- -------- ----------------------------
               EMPNO                                              NUMBER(4)
               ENAME                                              VARCHAR2(10)
               JOB                                                VARCHAR2(9)
               MGR                                                NUMBER(4)
               HIREDATE                                           DATE
               SAL                                                NUMBER(7,2)
               COMM                                               NUMBER(7,2)
               DEPTNO                                             NUMBER(2)
               COMB_ORD_FLG                              NOT NULL NUMBER(5)
               COMB_ORD_NO                               NOT NULL NUMBER(12)
              
              SQL> 
              What error are you getting?

              SY.
              • 4. Re: Alter Table Add column not null default value
                Hoek
                I tried this, but didn't work
                Unfortunatly you don't tell us why it didn't work.

                Assuming the table already contains data, yes, you'll have to split up.
                Something like:
                alter table dwsods01.dwt00301_ord_dtl_oms 
                add ( comb_ord_flg number(5,0) default 0
                    , comb_ord_no number(12,0) default 0 
                    ); 
                
                update dwsods01.dwt00301_ord_dtl_oms
                set    comb_ord_flg = 0    
                ,      comb_ord_no = 0;
                
                alter table dwsods01.dwt00301_ord_dtl_oms 
                modify ( comb_ord_flg not null
                       , comb_ord_no not null 
                       );
                • 5. Re: Alter Table Add column not null default value
                  Solomon Yakobson
                  hoek wrote:

                  Assuming the table already contains data, yes, you'll have to split up.
                  That would be true if defualt values were not supplied.

                  SY.
                  • 6. Re: Alter Table Add column not null default value
                    Hoek
                    Yes, you're right, just saw your example. Thanks.
                    Last monday I happened to have to add 2 not null columns myself, without a default, so I needed to split the operation.
                    That jumped immediatly into my mind. But that situation doesn't apply to OP.
                    • 7. Re: Alter Table Add column not null default value
                      670346
                      Fine,

                      You go for this and you will be waiting for a really long time as you are first inserting 0s in all the rows to just update all the 0s back to 0s again. So in a long table you will be multiplying your times by 2.

                      So, better do this:


                      alter table dwsods01.dwt00301_ord_dtl_oms
                      add ( comb_ord_flg number(5,0) null
                      , comb_ord_no number(12,0) null
                      );

                      update dwsods01.dwt00301_ord_dtl_oms
                      set comb_ord_flg = 0
                      , comb_ord_no = 0;

                      alter table dwsods01.dwt00301_ord_dtl_oms
                      modify ( comb_ord_flg not null
                      , comb_ord_no not null
                      );


                      LW
                      • 8. Re: Alter Table Add column not null default value
                        Hoek
                        Thanks, I already got the point.
                        I've had this before when posting untested examples... ;)
                        • 9. Re: Alter Table Add column not null default value
                          732390
                          The table already has huge data.

                          This is working

                          alter table dwsods01.dwt00301_ord_dtl_oms
                          add ( comb_ord_flg number(5,0) not null
                          , comb_ord_no number(12,0) not null
                          );

                          So after that should i have to write a statement for default like this

                          alter table dwsods01.dwt00301_ord_dtl_oms
                          modify ( comb_ord_flg default 0
                          , comb_ord_no default 0
                          );
                          • 10. Re: Alter Table Add column not null default value
                            670346
                            I am affraid that will not work.

                            Do the update for providing default values after the first alter and the finish with the last update for not null and future default values to 0.

                            LW
                            • 11. Re: Alter Table Add column not null default value
                              Solomon Yakobson
                              user10390682 wrote:
                              The table already has huge data.
                              And I bet you were not patient enough to wait for ALTER TABLE tofinish.
                              >
                              This is working

                              So after that should i have to write a statement for default like this

                              alter table dwsods01.dwt00301_ord_dtl_oms
                              modify ( comb_ord_flg default 0
                              , comb_ord_no default 0
                              );
                              The above will only apply to future inserts. Existing rows still have NULL as comb_ord_flg and comb_ord_no values. After running the above, you will have to issue:
                              update dwsods01.dwt00301_ord_dtl_oms set comb_ord_flg = 0,
                              comb_ord_no = 0
                              /
                              alter table dwsods01.dwt00301_ord_dtl_oms 
                              modify ( comb_ord_flg  not null
                                   , comb_ord_no not null 
                                   );
                              SY.
                              • 12. Re: Alter Table Add column not null default value
                                732390
                                I tried all the codes but the code which lawrence gave will work for my scenario i think
                                I ran only first statement of this code

                                alter table dwsods01.dwt00301_ord_dtl_oms
                                add ( comb_ord_flg number(5,0) null
                                , comb_ord_no number(12,0) null
                                );


                                update dwsods01.dwt00301_ord_dtl_oms
                                set comb_ord_flg = 0
                                , comb_ord_no = 0;


                                alter table dwsods01.dwt00301_ord_dtl_oms
                                modify ( comb_ord_flg not null
                                , comb_ord_no not null
                                );


                                But can i run the whole statement at a time or should i do three statements saperatley.
                                And one thing i didn't get is that at first he is making columns to null and setting to 0 and making not null. I didn't get logic in this

                                Edited by: user10390682 on Dec 10, 2009 8:51 AM
                                • 13. Re: Alter Table Add column not null default value
                                  Hoek
                                  Also:

                                  Keep in mind that, depending on how huge the data is, modifying columns to not null can be time consuming as well....
                                  • 14. Re: Alter Table Add column not null default value
                                    670346
                                    Good one.

                                    LW
                                    1 2 3 Previous Next