Deleting records


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: Deleting records

  1. #1
    Corine Guest

    Deleting records


    I know this is a stupid question, but I can't find out how to do it.....

    Two tables can be linked by a field. If they are in both tables, the records
    need to be deleted.
    In my delete-query only the first one is marked for deletion.

    How can I have them all deleted?

    PLEASE help, I am getting crazy!!!

    Corine, Amsterdam

  2. #2
    Vince Teachout Guest

    Re: Deleting records

    On 17 Jan 2001 00:57:01 -0800, "Corine" <c.van.boetzelaer@cable.a2000.nl> wrote:

    >Two tables can be linked by a field. If they are in both tables, the records
    >need to be deleted.
    >In my delete-query only the first one is marked for deletion.
    >
    >How can I have them all deleted?


    If you are referring to related tables, then you simple set the Cascade Option
    on the Delete Trigger in the Relation properties.

    If you are referring to a DELETE FROM Query, I believe you need to SQL
    statements (Assuming the above relation and trigger has NOT been sent)

    Would you send a code sample?

    >PLEASE help, I am getting crazy!!!

    Welcome to programming. :-)

    Ignore munged e-mail address above.
    If necessary to reply via e-mail, please reply to:
    caracal A T caracal D O T net

  3. #3
    Anders Altberg Guest

    Re: Deleting records

    DELETE FROM Table1 WHERE key = ANY (SELECT key FROM Table2)
    DELETE FROM Table1 WHERE EXISTS (SELECT * FROM Table2 ;
    WHERE Table2.key=Table1.key)
    But you want to delete these records in both tables perhaps
    SELECT key FROM Table1 JOIN Table2 ON Table1.key=Table2.key INTO CURSOR Q1
    DELETE FROM Table1 WHERE key = ANY (SELECT key FROM Q1)
    DELETE FROM Table2 WHERE key = ANY (SELECT key FROM Q1)

    -Anders

    "Corine" <c.van.boetzelaer@cable.a2000.nl> wrote in message
    news:3a655e5d$1@news.devx.com...
    >
    > I know this is a stupid question, but I can't find out how to do it.....
    >
    > Two tables can be linked by a field. If they are in both tables, the

    records
    > need to be deleted.
    > In my delete-query only the first one is marked for deletion.
    >
    > How can I have them all deleted?
    >
    > PLEASE help, I am getting crazy!!!
    >
    > Corine, Amsterdam




  4. #4
    Fred - Armoni Guest

    Re: Deleting records

    Anders Altberg a écrit :
    >
    > DELETE FROM Table1 WHERE key = ANY (SELECT key FROM Table2)


    Anders, is there a reason for using = ANY (SELECT...) instead of IN (SELECT...)
    ?

    Fred
    --
    FE AVP&Cie
    Juillenay, Europe

  5. #5
    Nancy Folsom Guest

    Re: Deleting records

    "Corine" <c.van.boetzelaer@cable.a2000.nl> wrote in
    <3a655e5d$1@news.devx.com>:

    >
    >I know this is a stupid question, but I can't find out how to do it.....
    >
    >Two tables can be linked by a field. If they are in both tables, the
    >records need to be deleted.
    >In my delete-query only the first one is marked for deletion.
    >
    >How can I have them all deleted?


    In order to use Cascade Delete, as Vince suggests, your tables need to
    belong to a DBC (database container).

    For more information, see "Creating Stored Procedures" in Chapter 6,
    Creating Databases and "Using Triggers" in Chapter 7, and Working with
    Tables, in the Programmer's Guide.

  6. #6
    Anders Altberg Guest

    Re: Deleting records

    Yes, to teach people a bit more SQL <g>. One would have to time different
    cases to see if one is more efficient than the other; I haven't.
    With the <operator> ANY comparison you can use >= Any or <ANY while IN and
    NOT IN only correspond to = ANY or <> ANY.
    -Anders

    "Fred - Armoni" <armoni@club-internet.fr> wrote in message
    news:3A65E822.24D7E0CA@club-internet.fr...
    > Anders Altberg a écrit :
    > >
    > > DELETE FROM Table1 WHERE key = ANY (SELECT key FROM Table2)

    >
    > Anders, is there a reason for using = ANY (SELECT...) instead of IN

    (SELECT...)
    > ?
    >
    > Fred
    > --
    > FE AVP&Cie
    > Juillenay, Europe




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center