Delete related records from multiple tables in MS Access in one sql statement. RRS feed

  • Question

  • I am using MS Access 2007 and want to delete all the matched records from 
    two tables in one sql statement.

    I am trying to execute the following query in c# with OLEDB drivers,
    but getting the error message "Record has been deleted".

    DELETE a1,  a2
    FROM tblProInfo as a1 INNER JOIN tblOrderAA as  a2
    ON a1.proInfoSerialNum = a2.orderAASerialPro
    WHERE a1.proInfoScienceName='shalosh'

    How I can make this possible without cascading referential integrity constraints.

    Tuesday, May 15, 2012 2:15 PM


  • Unfortunately you can not delete from multiple table simultaneously in a single delete statement. Your code still remains a single table delete, you just only specify which records from the first table will be deleted.

    You know the answer!!! it's all about cascading!!  so switch the delete on cascade on and cascaaaaaaaaade!!!

    • Proposed as answer by Mike FengModerator Wednesday, May 16, 2012 10:08 AM
    • Marked as answer by Thursday, May 17, 2012 7:40 AM
    Tuesday, May 15, 2012 3:27 PM