SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Deleting Joined records

    Hi

    Is it possible to delete joined records from multiple tables in a single SQL statement for MS Access?

    EG:

    Code:
    DELETE clients.*, bookings.* FROM clients, bookings WHERE clients.clientid=bookings.clientid and clients.clientid = 777
    Or

    Code:
    DELETE clients.*, bookings.* FROM clients INNER JOIN bookings On clients.clientid=bookings.clientid WHERE clients.clientid = 777
    This would seem to be the logical syntax, but it doesn't work.

    Obviously I can write two delete statements one for each record based on the [clientid] field, but this strikes me as a messy solution.
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  2. #2
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This may not help you but I'll give it a go.

    Does MS access support foreign key constraints? If you have them set up correctly and you delete the parent records, the db will automatically remove the child records in the other table. this should mean that you only need to
    Code:
    DELETE [name_of_pk] FROM clients cl
    WHERE cl.clientid = 777
    hth

    bazz

  3. #3
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It would seem Access doesn't support foreign key constraints, so with your code it just orphans the booking information.

    *UPDATE* WAIT actually it might, just not by default, you have to explicitly set it in the relationships... this might just work..
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com


Bookmarks

Posting Permissions

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