This discussion is archived
7 Replies Latest reply: Nov 22, 2012 11:13 PM by ranit B RSS

Copying records to another table column that has NULL values

ChristyH. Newbie
Currently Being Moderated
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-Consulting-com Expert
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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
    ChristyH. Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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
    ChristyH. Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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
    ChristyH. Newbie
    Currently Being Moderated
    Thanks all from the bottom of my heart

    Best regards

Legend

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