Results 1 to 3 of 3

Thread: Delete

  1. #1
    Join Date
    May 2013
    Posts
    33

    Unanswered: Delete

    Code:
    SQL> select * from employee;
    
    LASTNAME             DEPARTMENTID
    -------------------- ------------
    Rafferty                       31
    Jones                          33
    Steinberg                      33
    Robinson                       34
    Smith                          34
    John
    
    6 rows selected.
    
    SQL> select * from department;
    
    DEPARTMENTID DEPARTMENTNAME
    ------------ --------------------
              31 Sales
              33 Engineering
              34 Clerical
              35 Marketing
    
    SQL> select * from department d join employee e
      2  using (departmentid);
    
    DEPARTMENTID DEPARTMENTNAME       LASTNAME
    ------------ -------------------- --------------------
              31 Sales                Rafferty
              33 Engineering          Jones
              33 Engineering          Steinberg
              34 Clerical             Robinson
              34 Clerical             Smith
    
    SQL> delete from employee e
      2  where exists ( select * from department d join employee e
      3                 using (departmentid));
    
    6 rows deleted.
    .


    In the above structure, table EMPLOYEE contains NULL value in the departmentid column which refers the lastname of JOHN.

    when i delete EMPLOYEE table joining DEPARTMENT table using EXISTS, even the NULL value is also getting deleted .But i want JOHN record to be present while delting...I cant understand what mistake i did.

    Can anyone help me getting the right query.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    While the query select * from department d join employee e using (departmentid) returns at least one row, which it does, exists() will evaluate to true. You may want to correlate your delete with the subquery.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Can anyone help me getting the right query.
    You did not tell us which rows constitute the correct result set.
    A SQL which is right for me may not be right for you.
    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.

Posting Permissions

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