Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2008
    Posts
    3

    Unanswered: Regarding Delete in Access from multiple tables

    HI to all,

    I have 3 tables each containing StudentID as unique key,
    tables are student, status, personal,
    and i want to delete particular StudentID from all 3 tables simultaneously when one of the condition from Student table satisfies.
    i tried using the syntex.

    DELETE
    from Student,Status,Personal
    WHERE (Student.CSUID = Status.CSUID OR Student.CSUID = Personal.CSUID) AND Student.Group = "Biology";

    and in that it gives me error like
    specify the table containing the records you want to delete.

    if anybody can gives solution of this problem then it will be great cause i cant delete record one by one from table, and suppose i delete first from student then it cant be deleted from other tables.
    thanks in advance

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    thats not valid syntax for the delete
    you have 2 options in my book
    either
    issue a delete for each table containing the rows you wantr to delete

    eg
    CurrentDb.Execute ("delete from status where student_id = blah")
    CurrentDb.Execute ("delete from personal where student_id = blah")
    CurrentDb.Execute ("delete from Student where student_id = blah")

    OR
    when defining the relationship between the student table and the 'child' tables define the link as using delete cascade. ad just delete from student

    delete cascade deletes all records from the parent table AND any records in child table with the same student id.

    however delete cascade needs to be handled with care. you need to make certain that you don't loose any data that you need to keep.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2008
    Posts
    3
    Hi,
    thanks for reply

    actually i tried cascade delete but i am not getting it correctly, and here i am making query directly in access mean using SQL. so is it possible to cascade delete from database using SQL only? and if so then can u give me syntex of it?/

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    learn how to define relationships
    learn what relationships mean
    find where to define relationships in Access

    delete cascade isn't a function of SQL, its a function of how the realtionships are defined in the database

    anything that manipulates the data in the database is almost by definition SQL, whether its SQL you've written, or the way Access wraps it up and shades you from SQL.

    what have you tried so far
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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