Results 1 to 5 of 5

Thread: Oracle Error

  1. #1
    Join Date
    Oct 2011
    Posts
    16

    Smile Unanswered: Oracle Error

    Hello I'm New I'm Juanky and I'm Glad to be a new Member I'm An SelfStudent and hope i can get answer to my questions I was wondering if someone could help me I'm trying to delete a few rows from my database doing :

    DELETE FROM SalesReps
    WHERE HireDate = (SELECT HireDate
    FROM SalesReps
    WHERE HireDate <= '1989-01-01');
    and it comes to :
    ERROR at line 2:
    ORA-01427: single-row subquery returns more than one row

    I'm Not sure if this is a Group by or Order By error however is deleting Rows ? i have tried everything any help would be greatly apprecciated

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    DELETE FROM salesreps
    WHERE  hiredate IN (SELECT hiredate
                        FROM   salesreps
                        WHERE  hiredate <= '1989-01-01');
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2011
    Posts
    16

    Talking :o

    Amazing... THANKS! mm however I was almost sure i tried IN and <= maybe i didnt try them together ?? lol

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > WHERE hiredate <= '1989-01-01');

    In Oracle characters between single quote marks are STRINGS
    'this is a string, 2009-10-02, not a Date'
    When you need to convert STRING to DATE use TO_DATE() function.

    what date is '07-08-09'?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    wouldn't

    DELETE FROM SalesReps
    WHERE HireDate = (SELECT HireDate
    FROM SalesReps
    WHERE HireDate <= '1989-01-01');

    be the same as

    DELETE FROM SalesReps
    WHERE HireDate <= TO_DATE('1989-01-01','YYYY-MM-DD')
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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