1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

delete and

Discussion in 'SQL PL/SQL' started by monkey, Jun 3, 2014.

  1. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello!
    If I want to delete two rows, one where name = a and second one where surname is b, is that correct?

    delete from customers
    where name='a' and surname='b';

    ?

    Many thanks!!!
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No, as you could delete nothing from that table:


    Code (SQL):

    SQL> CREATE TABLE customers(
      2     custid NUMBER,
      3     name    varchar2(20),
      4     surname varchar2(20));


    TABLE created.


    SQL>
    SQL> INSERT ALL
      2  INTO customers(custid, name, surname)
      3  VALUES (1, 'a','a')
      4  INTO customers(custid, name, surname)
      5  VALUES (2,'b','b')
      6  INTO customers(custid, name, surname)
      7  VALUES(3,'c','c')
      8  SELECT * FROM dual;



    3 ROWS created.


    SQL> commit;


    Commit complete.


    SQL> DELETE FROM customers
      2  WHERE name='a' AND surname='b';


    0 ROWS deleted.


    SQL>
     

    Since no row has both name = 'a' and surname='b' the delete processes nothing. To do what you want:


    Code (SQL):

    SQL> DELETE FROM customers
      2  WHERE name = 'a' OR surname='b';


    2 ROWS deleted.


    SQL>
     

    OR needs to be used since you are referring to TWO rows, not one.
     
    monkey likes this.
  3. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Being understood is a grace! Thanks, Dave!:tup