7 Replies Latest reply: Nov 23, 2012 1:13 AM by ranit B RSS

    Copying records to another table column that has NULL values

    Christy H.
      Hello,

      I am trying to copy email address from one table to another table that has NULL value in email column
      SQL> select
        2     count(*)
        3  from
        4     users a, cvProperties b, cvDetails c
        5  where
        6     a.user_id=b.user_id and b.cv_id=c.cv_id and a.userType like '1'
        7  and
        8     c.email is NULL
        9  /
      
        COUNT(*)
      ----------
               7
      
      SQL> insert into cvDetails(email)
        2  VALUES(
        3  (select
        4     a.login
        5  from
        6     users a, cvProperties b, cvDetails c
        7  where
        8          a.user_id=b.user_id and b.cv_id=c.cv_id and a.userType like '1'
        9  and
       10          c.email is NULL)
       11  );
      (select
       *
      ERROR at line 3:
      ORA-01427: single-row subquery returns more than one row
      Please advise

      Thanks in anticipation
        • 1. Re: Copying records to another table column that has NULL values
          TPD-Opitz
          Should be
          SQL> insert into cvDetails(email)
            2  select
            4     a.login
            5  from
            6     users a, cvProperties b, cvDetails c
            7  where
            8          a.user_id=b.user_id and b.cv_id=c.cv_id and a.userType like '1'
            9  and
           10          c.email is NULL)
           11  ;
          • 2. Re: Copying records to another table column that has NULL values
            bencol
            You do not need the VALUES() when doing insert as select:
            INSERT INTO <table>
            (<columns>
            )
            SELECT ...
            ;
            • 3. Re: Copying records to another table column that has NULL values
              Christy H.
              I tried but same:
              SQL> insert into cvDetails(email)
                2  select
                3             a.login
                4  from
                5     users a, cvProperties b, cvDetails c
                6  where
                7     a.user_id=b.user_id and b.cv_id=c.cv_id and a.userType like '1'
                8  and
                9     c.email is NULL;
              
              7 rows created.
              
              SQL> commit;
              
              Commit complete.
              
              SQL> select
                2             count(*)
                3  from
                4     users a, cvProperties b, cvDetails c
                5  where
                6     a.user_id=b.user_id and b.cv_id=c.cv_id and a.userType like '1'
                7  and
                8     c.email is NULL;
              
                COUNT(*)
              ----------
                       7
              Thanks again

              Edited by: Christy H. on Nov 22, 2012 10:34 AM
              • 4. Re: Copying records to another table column that has NULL values
                rp0428
                >
                I am trying to copy email address from one table to another table that has NULL value in email column
                . . .
                I tried but same:
                >
                What other result would you expect? When you INSERT new rows it doesn't change any of the existing rows. You need to UPDATE the existing rows not insert new ones.

                We can't do any testing because you didn't provide the DDL for your tables but you want something along these lines
                UPDATE cvDetails c
                   SET EMAIL = (SELECT a.login from users a, cvProperties b
                   WHERE a.user_id=b.user_id and b.cv_id=c.cv_id and a.userType = '1')
                WHERE email IS NULL;
                Notice I replaced the LIKE with =. Why are you using LIKE if you have one distinct value?

                The above UPDATE only updates where email is null and it gets the value to update by querying the same tables you were querying.
                • 5. Re: Copying records to another table column that has NULL values
                  Christy H.
                  Hello,

                  Thanks for replying. Appreciated!!
                  SQL> select
                    2     count(*)
                    3  from
                    4     users a, cvProperties b, cvDetails c
                    5  where
                    6     a.user_id=b.user_id and b.cv_id=c.cv_id and a.userType like '1'
                    7  and
                    8     c.email is NULL
                    9  /
                  
                    COUNT(*)
                  ----------
                           7
                  
                  SQL> UPDATE cvDetails c
                    2      SET EMAIL = (
                    3     SELECT a.login from users a, cvProperties b
                    4             WHERE a.user_id=b.user_id and b.cv_id=c.cv_id and a.userType = 1
                  
                    5     )
                    6   WHERE email IS NULL;
                  
                  800 rows updated.
                  
                  SQL> rollback;
                  
                  Rollback complete.
                  
                  SQL> desc users
                   Name                                      Null?    Type
                   ----------------------------------------- -------- ----------------------------
                  
                   USER_ID                                   NOT NULL NUMBER(14)
                   AGREE_ID                                           NUMBER(14)
                   COUNTRY_ID                                         NUMBER(14)
                   REGISTRATIONDATE                                   TIMESTAMP(0)
                   USERTYPE                                           NUMBER(2)
                   STATUS                                             NUMBER(2)
                   NAME                                               VARCHAR2(36)
                   LOGIN                                              VARCHAR2(110)
                   PASSWORD                                           VARCHAR2(15)
                   FOLDERID                                           NUMBER(14)
                   FOLDERCREATIONDATE                                 DATE
                  
                  SQL> desc cvProperties
                   Name                                      Null?    Type
                   ----------------------------------------- -------- ----------------------------
                  
                   CV_ID                                     NOT NULL NUMBER(14)
                   USER_ID                                            NUMBER(14)
                   CAT_ID                                             NUMBER(14)
                   EMPLOYERORAGENT_ID                                 NUMBER(14)
                   STAFF_ID                                           NUMBER(14)
                   CVDATE                                             TIMESTAMP(0)
                   CVNAME                                             VARCHAR2(230)
                   STATUS                                             NUMBER(3)
                   PREV_CVID                                          NUMBER(14)
                   ISEDITED                                           NUMBER(2)
                   CVSOURCE                                           NUMBER(2)
                  
                  SQL> desc cvDetails
                   Name                                      Null?    Type
                   ----------------------------------------- -------- ----------------------------
                  
                   CD_ID                                     NOT NULL NUMBER(14)
                   CV_ID                                              NUMBER(14)
                   NATIONALITY                                        VARCHAR2(230)
                   OBJECTIVES                                         VARCHAR2(3900)
                   NAME                                               VARCHAR2(230)
                   FATHERNAME                                         VARCHAR2(230)
                   MOTHERNAME                                         VARCHAR2(230)
                   DOB                                                VARCHAR2(230)
                   GENDER                                             VARCHAR2(230)
                   MSTATUS                                            VARCHAR2(230)
                   PASSPORTNO                                         VARCHAR2(161)
                   EMAIL                                              VARCHAR2(230)
                   PHONE                                              VARCHAR2(75)
                   MOBILE                                             VARCHAR2(25)
                   ADDRESS                                            VARCHAR2(2500)
                   STATE                                              VARCHAR2(230)
                   ZIPCODE                                            VARCHAR2(230)
                   CITY                                               VARCHAR2(230)
                   EDUCATION                                          CLOB
                   EXPERIENCE                                         CLOB
                   SKILLS                                             CLOB
                   LANGUAGES                                          VARCHAR2(1400)
                   HOBBIES                                            VARCHAR2(3200)
                   ACHIEVEMENTS                                       VARCHAR2(3900)
                   REFERENCES                                         VARCHAR2(3900)
                  You said well that despite inserting we must update table but its updating 800 rows instead of 7. I must doing a major mistake. Please check DDL of my tables

                  Thanks again and best regards
                  • 6. Re: Copying records to another table column that has NULL values
                    ranit B
                    Check if this helps...
                    MERGE INTO cvdetails c
                         USING (SELECT a.login, b.cv_id
                                  FROM users a, cvproperties b
                                 WHERE a.user_id = b.user_id AND a.usertype = 1) xx
                            ON (xx.cv_id = c.cv_id)
                    WHEN MATCHED
                    THEN
                       UPDATE SET email = xx.login
                               WHERE c.email IS NULL;
                    Ranit B.
                    • 7. Re: Copying records to another table column that has NULL values
                      Christy H.
                      Thanks all from the bottom of my heart

                      Best regards