1 2 Previous Next 18 Replies Latest reply: Oct 10, 2012 8:02 AM by myOra_help RSS

    SQL Query

    940673
      Hi Team,

      I am using oracle version 10g. I have a table like below:-


      SQL> select * from t3;

      ID First_Name Last_Name Password
      1     Sri          Cha Sunday@123
      2     Ari     Cha Sunday@123
      3 Raj     Cha     Sunday@123

      I want to duplicate a row except the First Name of the table. I mean when I insert a row only I the first_name will be provided me and the other rows will be same like other.

      For example when I insert a next row I will provide the first_name only and rest of the rows will remain intact.

      After inserting:-

      SQL> select * from t3;

      ID First_Name Last_Name Password
      1     Sri          Cha Sunday@123
      2     Ari     Cha Sunday@123
      3 Raj     Cha     Sunday@123
      4 Ank Cha Sunday@123


      Can I achieve this in a single insert statement?? Please help.

      Thanks In Advance
        • 1. Re: SQL Query
          Bawer
          937670 wrote:

          Can I achieve this in a single insert statement?? Please help.
          insert into t3 values
          select new_id, 'new_first_name', last_name, password from t3 where id = 1
          • 2. Re: SQL Query
            Chanchal Wankhade
            one of solution is use default(values) by modifying table.
            • 3. Re: SQL Query
              Chanchal Wankhade
              one of the option would be

              alter table modify column with default values.
              • 4. Re: SQL Query
                Ashu_Neo
                Try this...
                INSERT INTO t3
                SELECT 4 id, 'Ank' first_name, last_name, password
                FROM t3
                WHERE  ROWID =(SELECT MAX(ROWID) FROM t3)
                /
                Thanks!
                • 5. Re: SQL Query
                  Mr Lonely
                  Question

                  1. How you will get the ID? do you have any sequence for that?
                  2. Why don't you put default values for rest of the columns
                  3. As an alternate you may create one procedure and pass the first_name which will populate the table the way you want it.
                  4. Also put unique index on first_name column so that you are not ending up with duplicate first name

                  btw if you don't have sequence and want to pass the name as variable you may try the below query
                  insert into t3
                  select max(id)+1, '&v_name', 'Cha', 'Sunday@123' from t3;
                  • 6. Re: SQL Query
                    Mr Lonely
                    are you sure the syntax is correct? Also you have fixed id and name value. Have you read the requirement clearly?
                    • 7. Re: SQL Query
                      jeneesh
                      What is the business logic you are trying to handle?

                      You can do some thing like below(Assuming T3 is not empty). But, it wont work in a multi user environment...Explain your issue clearly so that people can help you better
                      INSERT INTO t3
                      (ID ,First_Name, Last_Name ,Password)
                      SELECT max(id)+1, 'Ank', max(last_name), max(password)
                      FROM t3
                      And password is stored as it is..?

                      Edited by: jeneesh on Oct 10, 2012 2:42 PM
                      • 8. Re: SQL Query
                        940673
                        Hi,

                        Thanks for your reply. This way I can achieve this..

                        But if my table is having more than 100 columns then I have to write 100 columns in the select query.

                        For example

                        insert into t3
                        select new_id,'new_first_name',c3, c4, c5, c6....c100 from t3;

                        I want to avoid writing columns c3 till c100.

                        Can i achive it..

                        Thanks
                        • 9. Re: SQL Query
                          jeneesh
                          Generate the select list from the data dictionary view ALL_TAB_COLS
                          • 10. Re: SQL Query
                            940673
                            Can we perform this in some other way.. I am using a normal oracle user who is having only create session,resouce privilege...

                            I can't describe a data dictionary view.

                            Please suggest some other step.

                            Thanks
                            • 11. Re: SQL Query
                              940673
                              Out of hundred columns I want to enter only the id which will be max(id) of table +1 and the new name column , rest all the columns I don't want to enter manually.
                              • 12. Re: SQL Query
                                jeneesh
                                937670 wrote:
                                Can we perform this in some other way.. I am using a normal oracle user who is having only create session,resouce privilege...

                                I can't describe a data dictionary view.
                                Really?

                                Enter user-name: system@******
                                Enter password:
                                Connected.
                                SQL> create user user1 identified by user1;
                                
                                User created.
                                
                                SQL> grant create session to user1;
                                
                                Grant succeeded.
                                
                                SQL> grant resource to user1;
                                
                                Grant succeeded.
                                
                                SQL> conn
                                Enter user-name: user1@******
                                Enter password:
                                Connected.
                                SQL> desc all_tab_cols;
                                 Name                                                              Null?    Type
                                 ----------------------------------------------------------------- -------- ------------------------
                                 OWNER                                                             NOT NULL VARCHAR2(30)
                                 TABLE_NAME                                                        NOT NULL VARCHAR2(30)
                                 COLUMN_NAME                                                       NOT NULL VARCHAR2(30)
                                 DATA_TYPE                                                                  VARCHAR2(106)
                                 DATA_TYPE_MOD                                                              VARCHAR2(3)
                                 DATA_TYPE_OWNER                                                            VARCHAR2(30)
                                 DATA_LENGTH                                                       NOT NULL NUMBER
                                 DATA_PRECISION                                                             NUMBER
                                 DATA_SCALE                                                                 NUMBER
                                 NULLABLE                                                                   VARCHAR2(1)
                                 COLUMN_ID                                                                  NUMBER
                                 DEFAULT_LENGTH                                                             NUMBER
                                 DATA_DEFAULT                                                               LONG
                                 NUM_DISTINCT                                                               NUMBER
                                 LOW_VALUE                                                                  RAW(32)
                                 HIGH_VALUE                                                                 RAW(32)
                                 DENSITY                                                                    NUMBER
                                 NUM_NULLS                                                                  NUMBER
                                 NUM_BUCKETS                                                                NUMBER
                                 LAST_ANALYZED                                                              DATE
                                 SAMPLE_SIZE                                                                NUMBER
                                 CHARACTER_SET_NAME                                                         VARCHAR2(44)
                                 CHAR_COL_DECL_LENGTH                                                       NUMBER
                                 GLOBAL_STATS                                                               VARCHAR2(3)
                                 USER_STATS                                                                 VARCHAR2(3)
                                 AVG_COL_LEN                                                                NUMBER
                                 CHAR_LENGTH                                                                NUMBER
                                 CHAR_USED                                                                  VARCHAR2(1)
                                 V80_FMT_IMAGE                                                              VARCHAR2(3)
                                 DATA_UPGRADED                                                              VARCHAR2(3)
                                 HIDDEN_COLUMN                                                              VARCHAR2(3)
                                 VIRTUAL_COLUMN                                                             VARCHAR2(3)
                                 SEGMENT_COLUMN_ID                                                          NUMBER
                                 INTERNAL_COLUMN_ID                                                NOT NULL NUMBER
                                 HISTOGRAM                                                                  VARCHAR2(15)
                                 QUALIFIED_COL_NAME                                                         VARCHAR2(4000)
                                • 13. Re: SQL Query
                                  940673
                                  Hi Janeesh,

                                  I apologize for not mentioning my requirement clearly. My table is having more than 100 columns. Every time user will pass a new value and an auto generated id will be inserted in the table. Rest of the columns will have default values.

                                  My concern is while writing the insert statement I want to enter only the id which will be max(id)+1 and new user name. Also I am trying to avoid writing rest of the columns.

                                  such like

                                  insert into t3
                                  select max(id)+1,'New_Name', * from t3; something like that.

                                  Can I achieve this..

                                  Thanks
                                  • 14. Re: SQL Query
                                    jeneesh
                                    937670 wrote:
                                    Hi Janeesh,

                                    I apologize for not mentioning my requirement clearly. My table is having more than 100 columns. Every time user will pass a new value and an auto generated id will be inserted in the table. Rest of the columns will have default values.

                                    My concern is while writing the insert statement I want to enter only the id which will be max(id)+1 and new user name. Also I am trying to avoid writing rest of the columns.

                                    such like

                                    insert into t3
                                    select max(id)+1,'New_Name', * from t3; something like that.

                                    Can I achieve this..

                                    Thanks
                                    You cannot do that..

                                    You have to list the columns you want to insert..

                                    Otherwise also, using "select *" in an insert statement is a bad idea. What if a new column is added in your table. Your INSERT statemnts will become invalid, even if you require only the existing column to be populated...

                                    You should be always using
                                    insert into table_name (column list...)
                                    select column_list..
                                    from ..
                                    1 2 Previous Next