Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2004
    Posts
    127

    Unanswered: SQL UPDATE/INSERT problem

    Hi,

    Im reducing the size of a varchar2 column. ive copied data from the column to be shrunk to a temp loc, reduced the actual column to be reduced only i cannot seem to insert the data back into the reduced column.

    ive tried the following two statements, each to no avail:

    INSERT INTO T (ENAME) AS SELECT ENAME FROM T_COPY

    *this inserts data on a new row, not the same row


    SQL> update T SET ENAME = (SELECT * FROM T_COPY);
    update T SET ENAME = (SELECT * FROM T_COPY)

    ERROR at line 1:
    ORA-01427: single-row subquery returns more than one row


    can someone please offer a pointer or helping hand. thanks in advance!

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Did you copy the PK aswell as ename otherwise how do you tie the data back to where it is suppossed to go? If you do have the PK copied out to your temp table then do

    update T set ename=(select ename from t_copy c where <c.PK=T.PK>)

    Alan

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Simply, Oracle doesn't know which records to update.

    If your first step was like this:
    Code:
    CREATE TABLE t_copy AS 
    SELECT ename FROM t;
    you are in deep trouble, unless you have backup of the original "t" table (before you did anything about it). Why? How are you supposed to know which "ename" belongs to which record?

    But, if it was something like this:
    Code:
    CREATE TABLE t_copy AS 
    SELECT enumber, ename FROM t;
    it is quite simple to update desired records:
    Code:
    UPDATE t SET
    t.ename = (SELECT c.ename 
               FROM t_copy c
               WHERE c.enumber = t.enumber);
    As it is not likely that there are different records in those tables, I guess you won't need additional WHERE clause in UPDATE statement (using either IN or EXISTS).

    [EDIT] As it took some time to write this post, you've already heard all about it from Alan ...

  4. #4
    Join Date
    Jun 2004
    Posts
    127
    thanks very much chaps!

    my other idea to get round the prob was to make a full copy of table T as T_COPY, then truncate T, reduce the necessary column in T & then insert all contents of T_COPY into T.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    May I ask WHY do you want to reduce a VARCHAR2 column? For example, VARCHAR2(10) doesn't take more space than VARCHAR2(255). I would understand if it was a CHAR column (and you wanted to change it into a VARCHAR2 one), but I don't quite get your idea.

    [EDIT] This reminds me of another post here some time ago, where someone wanted to save space in his database by reducing column name length (literally it was reducing, for example, RBR into RB)
    Last edited by Littlefoot; 06-26-06 at 07:59.

  6. #6
    Join Date
    Jun 2004
    Posts
    127
    [QUOTE=Littlefoot]May I ask WHY do you want to reduce a VARCHAR2 column? For example, VARCHAR2(10) doesn't take more space than VARCHAR2(255). I would understand if it was a CHAR column (and you wanted to change it into a VARCHAR2 one), but I don't quite get your idea.

    im practicing for 1z0 007. im trying to get a feel for what i can and cannot do with SQL.

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Oh, that's nice. Good luck!

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Why do you copy the data around? You can shrink the data in-place and then reduce the column's size:
    Code:
    UPDATE theTable SET ename = substr(ename,20);
    ALTER TABLE theTable MODIFY (ename varchar2(20));
    If you use a multi-byte character set, then you might need
    Code:
    ALTER TABLE theTable MODIFY (ename varchar2(20 char));
    to specify the new length in characters rather then bytes.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •